SQL Server 2005 Using CHARINDEX() To split a strin

问题:

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)

回答1:

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.

回答2:

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

回答3:

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

回答4:

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

回答5:

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**

回答6:

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