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)

You can use the `substring` function and increase the start index with the charindex of the first, second, third hit and so on. — jpw, Jul 09 '13 at 18:44
@RaduGheorghiu By searching for the dash in the substring that starts from the charindex of the first dash + 1. — jpw, Jul 09 '13 at 18:49
"Delete" and "Deleta" are not part of the NATO alphabet (FTFY). Also, aren't numbers supposed to be given like "Two Three" not "Twenty Three"? — ErikE, Jul 09 '13 at 18:53
If numbers are supposed to be given like `Two`, `Three` why just don't chain a all the NATO alphabet like ...`REPLACE`? `REPLACE(REPLACE(@Item, 'a', 'Alpha '), 'b', 'Bravo')...` from `[a-z][0-9][signs...] — Luis LL, Jul 09 '13 at 18:59
@RaduGheorghiu Then it wouldn't work obviously. The number of dashes has to be known before, but that is true for every solution that use hard coded values and not some kind of iteration. — jpw, Jul 09 '13 at 18:59
@jpw Then I agree, it's just personally I prefer generic solutions. — Radu Gheorghiu, Jul 09 '13 at 19:08
@user2531854 You should take a look at [Split strings the right way – or the next best way](http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings), written by SQL guru Aaron Bertrand. It gives several good solutions for the splitting problem. — jpw, Jul 09 '13 at 19:10
@LuisLL That is not going to work. Once you get to `'e', 'Echo'` you'd replace the `e` in `Charlie` and get `CharliEcho`, and so on. It would be a mess. See the result on `ABCDEFGHIJKLMNOPQRSTUVWXYZ` in a [SQL fiddle](http://sqlfiddle.com/#!3/d41d8/16838). — ErikE, Jul 09 '13 at 19:19

Gordon Linoff · Accepted Answer · 2013-07-09T18:55:07.527

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.

ErikE · Answer 2 · 2016-06-16T15:30:39.387

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).

Cool function. I can't seem to run it on SQL 2005.I get Msg 156, Level 15, State 1, Procedure NATOEncode, Line 21 Incorrect syntax near the keyword 'VALUES'. — user2531854, Jul 09 '13 at 19:45
In SQL 2005 change that to `INNER JOIN (SELECT 'A', 'Alpha' UNION ALL SELECT 'B', 'Beta' UNION ALL ...)`. — ErikE, Jul 09 '13 at 20:14
Nine is not used in the NATO alphabet because it can be confused with "no" in German (Nein) which sounds like nine. The correct equivalent for the number 9 in the NATO alphabet is "niner". Cheers! — Mario Levesque, Jun 16 '16 at 15:03
@levteck You are so right! I even knew that but just rushed too quickly, I guess. Fixed, and thanks! — ErikE, Jun 16 '16 at 15:31

score 2 · Answer 3 · edited Mar 29 '17 at 18:18

2

Try the following query:

DECLARE @item VARCHAR(MAX) = 'LD-23DSP-1430'

SELECT
SUBSTRING( @item, 0, CHARINDEX('-', @item)) ,
SUBSTRING(
               SUBSTRING( @item, CHARINDEX('-', @item)+1,LEN(@ITEM)) ,
               0 ,
               CHARINDEX('-', SUBSTRING( @item, CHARINDEX('-', @item)+1,LEN(@ITEM)))
              ),
REVERSE(SUBSTRING( REVERSE(@ITEM), 0, CHARINDEX('-', REVERSE(@ITEM))))

edited Mar 29 '17 at 18:18

Peter Mortensen

30,738
21
105
131

answered Jul 09 '13 at 18:56

Hiren Dhaduk

2,760
2
19
21

Thanks for the answer! Another question. What if an item has mutiple dashes. So it could be LD-23DSP-MF-1430 Is there a way to count how many dashes and break each section down into a field? – user2531854 Jul 09 '13 at 19:04

score 0 · Answer 4 · answered May 02 '14 at 09:55

    USE [master]
    GO
    /******  this function returns Pakistan where as if you want to get ireland simply replace (SELECT SUBSTRING(@NEWSTRING,CHARINDEX('$@$@$',@NEWSTRING)+5,LEN(@NEWSTRING))) with
SELECT @NEWSTRING = (SELECT SUBSTRING(@NEWSTRING, 0,CHARINDEX('$@$@$',@NEWSTRING)))******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[FN_RETURN_AFTER_SPLITER] 
    (  
     @SPLITER varchar(max))
    RETURNS VARCHAR(max)
    AS 
    BEGIN

    --declare @testString varchar(100),
    DECLARE @NEWSTRING VARCHAR(max) 
    -- set @teststring = '@ram?eez(ali)'
     SET @NEWSTRING = @SPLITER ; 

    SELECT @NEWSTRING = (SELECT SUBSTRING(@NEWSTRING,CHARINDEX('$@$@$',@NEWSTRING)+5,LEN(@NEWSTRING)))
    return @NEWSTRING 
    END
    --select [dbo].[FN_RETURN_AFTER_SPLITER]  ('Ireland$@$@$Pakistan')

score 0 · Answer 5 · edited Jun 17 '14 at 12:47

Create FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(200), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(10) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 

END**strong text**

Kandarp Patel · Answer 6 · 2014-12-17T09:57:57.913

-1

DECLARE @variable VARCHAR(100) = 'LD-23DSP-1430';
WITH    Split
      AS ( SELECT   @variable AS list ,
                    charone = LEFT(@variable, 1) ,
                    R = RIGHT(@variable, LEN(@variable) - 1) ,
                    'A' AS MasterOne
           UNION ALL
           SELECT   Split.list ,
                    LEFT(Split.R, 1) ,
                    R = RIGHT(split.R, LEN(Split.R) - 1) ,
                    'B' AS MasterOne
           FROM     Split
           WHERE    LEN(Split.R) > 0
         )
SELECT  *
FROM    Split
OPTION  ( MAXRECURSION 10000 );

edited Dec 17 '14 at 09:57

answered Dec 17 '14 at 09:50

Kandarp Patel

1
1

Hi @Kandarp Patel, your solution does not meet the question requirements. – Nani3 Oct 15 '20 at 12:32

SQL Server 2005 Using CHARINDEX() To split a string

6 Answers6

Linked