user6780741 user6780741 - 1 year ago 82
SQL Question

How can I "dynamically" split a varchar column by specific characters?

I have a column that stores 2 values. Example below:

| Column 1 |
|some title1 =ExtractThis ; Source Title12 = ExtractThis2|

I want to remove 'ExtractThis' into one column and 'ExtractThis2' into another column. I've tried using a substring but it doesn't work as the data in column 1 is variable and therefore it doesn't always carve out my intended values. SQL below:

SELECT substring(d.Column1,13,24) FROM dbo.Table d

This returns 'Extract This' but for other columns it either takes too much or too little. Is there a function or combination of functions that will allow me to split consistently on the character? This is consistent in my column unlike my length count.

Answer Source
select substring(col1,CHARINDEX('=',col1)+1,CHARINDEX (';',col1)-CHARINDEX ('=',col1)-1) Val1,
        substring(col1,CHARINDEX('=',col1,CHARINDEX (';',col1))+1,LEN(col1)) Val2
from #data

there is duplicate calculation that can be reduced from 5 to 3 to each line. but I want to believe this simple optimization done by SQL SERVER.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download