SQL Server 2005 Using CHARINDEX() To split a string

Question

How can I split the following string based on the '-' character?

So if I had this string: LD-23DSP-1430

How could I split it into separate columns like this:

LD        23DSP       1430

Also, is there a way to split each character into a separate field if I needed to (without the '-')? I'm trying to find a way to replace each letter with the NATO alphabet.

So this would be..... Lima Delta Twenty Three Delta Sierra Papa Fourteen Thirty.... in one field.

I know I can get the left side like this:

LEFT(@item, CHARINDEX('-', @item) - 1)

Answer 1

Here's a little function that will do "NATO encoding" for you:

CREATE FUNCTION dbo.NATOEncode (
   @String varchar(max)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
   WITH L1 (N) AS (SELECT 1 UNION ALL SELECT 1),
   L2 (N) AS (SELECT 1 FROM L1, L1 B),
   L3 (N) AS (SELECT 1 FROM L2, L2 B),
   L4 (N) AS (SELECT 1 FROM L3, L3 B),
   L5 (N) AS (SELECT 1 FROM L4, L4 C),
   L6 (N) AS (SELECT 1 FROM L5, L5 C),
   Nums (Num) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM L6)
   SELECT
      NATOString = Substring((
         SELECT
            Convert(varchar(max), ' ' + D.Word)
         FROM
            Nums N
            INNER JOIN (VALUES
               ('A', 'Alpha'),
               ('B', 'Beta'),
               ('C', 'Charlie'),
               ('D', 'Delta'),
               ('E', 'Echo'),
               ('F', 'Foxtrot'),
               ('G', 'Golf'),
               ('H', 'Hotel'),
               ('I', 'India'),
               ('J', 'Juliet'),
               ('K', 'Kilo'),
               ('L', 'Lima'),
               ('M', 'Mike'),
               ('N', 'November'),
               ('O', 'Oscar'),
               ('P', 'Papa'),
               ('Q', 'Quebec'),
               ('R', 'Romeo'),
               ('S', 'Sierra'),
               ('T', 'Tango'),
               ('U', 'Uniform'),
               ('V', 'Victor'),
               ('W', 'Whiskey'),
               ('X', 'X-Ray'),
               ('Y', 'Yankee'),
               ('Z', 'Zulu'),
               ('0', 'Zero'),
               ('1', 'One'),
               ('2', 'Two'),
               ('3', 'Three'),
               ('4', 'Four'),
               ('5', 'Five'),
               ('6', 'Six'),
               ('7', 'Seven'),
               ('8', 'Eight'),
               ('9', 'Niner')
            ) D (Digit, Word)
               ON Substring(@String, N.Num, 1) = D.Digit
         WHERE
            N.Num <= Len(@String)
         FOR XML PATH(''), TYPE
      ).value('.[1]', 'varchar(max)'), 2, 2147483647)
);

This function will work on even very long strings, and performs pretty well (I ran it against a 100,000-character string and it returned in 589 ms). Here's an example of how to use it:

SELECT NATOString FROM dbo.NATOEncode('LD-23DSP-1430');
-- Output: Lima Delta Two Three Delta Sierra Papa One Four Three Zero

I intentionally made it a table-valued function so it could be inlined into a query if you run it against many rows at once, just use CROSS APPLY or wrap the above example in parentheses to use it as a value in the SELECT clause (you can put a column name in the function parameter position).

Answer 2

I wouldn't exactly say it is easy or obvious, but with just two hyphens, you can reverse the string and it is not too hard:

with t as (select 'LD-23DSP-1430' as val)
select t.*,
       LEFT(val, charindex('-', val) - 1),
   SUBSTRING(val, charindex('-', val)+1, len(val) - CHARINDEX('-', reverse(val)) - charindex('-', val)),
       REVERSE(LEFT(reverse(val), charindex('-', reverse(val)) - 1))
from t;

Beyond that and you might want to use split() instead.