How to split string using delimiter char using T-SQL

Question

I have this long string in one of the columns of the table  I want to get only specific information - My Table structure -  Col1    123  Col2    AAAAA  Col3    Clent ID   4356hy Client Name   B B BOB Client Phone   667-444-2626 Client Fax   666-666-0151 Info   INF8888877 -MAC333330554 444400800    My select statement is -  Select col1  col2  col3 from Table01   But in Col3 I just need  Client Name s value which is  B B BOB     In Col3 -   Column delimiter is     pipe char  eg   Client ID   4356hy    Key Value delimiter is       equal to sign with one white space  leading and trailing     Please help

User · Accepted Answer

For your specific data  you can use  Select col1  col2  LTRIM RTRIM SUBSTRING      STUFF col3  CHARINDEX      col3      PATINDEX    Client Name      col3    14   1000           PATINDEX    Client Name      col3    14  1000    col3 from Table01   EDIT - charindex vs patindex  Test  select col3  Clent ID   4356hy Client Name   B B BOB Client Phone   667-444-2626 Client Fax   666-666-0151 Info   INF8888877 -MAC333330554 444400800  into t1m from master  spt values a cross join master  spt values b where a number  lt  100 --  711704 row s  affected   set statistics time on  dbcc dropcleanbuffers dbcc freeproccache select a CHARINDEX   Client Name     col3  into  tmp1 from t1m drop table  tmp1  dbcc dropcleanbuffers dbcc freeproccache select a PATINDEX    Client Name      col3  into  tmp2 from t1m drop table  tmp2  set statistics time off   Timings  CHARINDEX    SQL Server Execution Times  1      CPU time   5656 ms   elapsed time   6418 ms   SQL Server Execution Times  2      CPU time   5813 ms   elapsed time   6114 ms   SQL Server Execution Times  3      CPU time   5672 ms   elapsed time   6108 ms   PATINDEX    SQL Server Execution Times  1      CPU time   5906 ms   elapsed time   6296 ms   SQL Server Execution Times  2      CPU time   5860 ms   elapsed time   6404 ms   SQL Server Execution Times  3      CPU time   6109 ms   elapsed time   6301 ms    Conclusion  The timings for CharIndex and PatIndex for 700k calls are within 3 5  of each other  so I don t think it would matter whichever is used  I use them interchangeably when both can work

User · Answer

It is terrible  but you can try to use   select SUBSTRING Table1 Col1 0 PATINDEX        Table1 Col1   as myString from Table1   This code is probably not 100  right though  need to be adjusted

User · Answer

You need a split function   SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO Create Function  dbo   udf Split            DelimitedList nvarchar max         Delimiter nvarchar 2          RETURNS TABLE  AS RETURN            With CorrectedList As                   Select Case When Left  DelimitedList  Len  Delimiter    lt  gt   Delimiter Then  Delimiter Else    End                DelimitedList               Case When Right  DelimitedList  Len  Delimiter    lt  gt   Delimiter Then  Delimiter Else    End             As List               Len  Delimiter  As DelimiterLen                     Numbers As                    Select TOP  Coalesce DataLength  DelimitedList  2 0    Row Number   Over   Order By c1 object id   As Value         From sys columns As c1             Cross Join sys columns As c2               Select CharIndex  Delimiter  CL list  N Value    CL DelimiterLen As Position           Substring                       CL List                       CharIndex  Delimiter  CL list  N Value    CL DelimiterLen                            CharIndex  Delimiter  CL list  N Value   1                                                     -   CharIndex  Delimiter  CL list  N Value    CL DelimiterLen                          As Value     From CorrectedList As CL         Cross Join Numbers As N     Where N Value  lt   DataLength CL List    2         And Substring CL List  N Value  CL DelimiterLen     Delimiter         With your split function  you would then use Cross Apply to get the data   Select T Col1  T Col2       Substring  Z Value  1  Charindex        Z Value  - 1   As AttributeName       Substring  Z Value  Charindex        Z Value    1  Len Z Value    As Value From Table01 As T     Cross Apply dbo udf Split  T Col3        As Z

User · Answer

You simply need to do a SUBSTR on the string in col3          Select col1  col2  REPLACE substr col3  instr col3   Client Name          instr col3       instr col3   Client Name    -     instr col3   Client Name            Client Name              from Table01    And yes  that is a bad DB design for the reasons stated in the original issue

[sql] How to split string using delimiter char using T-SQL?

EDIT - charindex vs patindex

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql