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.

Be prepared for a lot of comments on how terrible that design is :) — JNK
– JNK, Commented Feb 23, 2011 at 20:28
To pick up @JNK's ball: THAT'S A TERRIBLE DESIGN! But you can still save it, if you change the table structure to include just a foreign key to a client table that holds all that data... — Christian Severin
– Christian Severin, Commented Feb 23, 2011 at 20:36

RichardTheKiwi · Accepted Answer · 2011-02-23 21:51:43Z

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

Isn't PATINDEX('%|Client Name =%', col3) the same as CHARINDEX('|Client Name =', col3) and isn't the latter supposed to be faster than the former?

Thomas · Accepted Answer · 2011-02-23 20:34:12Z

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)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @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 <= 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

This function doesn't give consistent results. See stackoverflow.com/q/12480321 for issues, including a fiddle that demonstrates the problem.

MikeTWebb · Accepted Answer · 2011-02-23 20:29:38Z

0

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

answered Feb 23, 2011 at 20:29

MikeTWebb

9,32726 gold badges97 silver badges134 bronze badges

Comments

user194076 · Accepted Answer · 2011-02-23 20:29:56Z

0

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

answered Feb 23, 2011 at 20:29

user194076

9,05524 gold badges103 silver badges158 bronze badges

Collectives™ on Stack Overflow

How to split string using delimiter char using T-SQL?

4 Answers 4

EDIT - charindex vs patindex

1 Comment

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

EDIT - charindex vs patindex

1 Comment

1 Comment

Comments

Comments

Linked

Related