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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  0            SELECT    FROM    Split OPTION    MAXRECURSION 10000

User · Answer

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

[sql] SQL Server 2005 Using CHARINDEX() To split a string

Examples related to sql

Examples related to sql-server-2005