Spit column that has underscore to multiple columns and also calculate age

Question

I have a table called clients and I'm trying to split the value which contains underscores that is one column into multiple columns and I'm also trying to create a column that calculates the age of the person.

Here is how the table looks like:

USERID  Vendors  (dobyr)  login  source
10bta   yes      1976     yes    google_hope
25cwd   yes      1986     yes    google_hln_1045
45tyj   no       1990     no     google_hln_4345
645io   no       1960     no     google

The goal is to have that look like this:

USERID  Vendors  (dobyr)  login  Source1  Source2  Source3  Age
10bta   yes      1976     yes    google   hope     null      44
25cwd   yes      1986     yes    google   hln      1045      34
45tyj   no       1992     no     google   hln      4345      28
645io   no       1960     no     google   null     null      30

Unfortunately, when using my code google doesn't stay in source1.

This is the code that I used:

 select *, datepart(year, CURRENT_TIMESTAMP)-dobyr AS Age,
 parsename(replace(source,'_','.'),1) AS source_1
,parsename(replace(source,'_','.'),2) AS source_2
,parsename(replace(source,'_','.'),3) AS source_3
,parsename(replace(source,'_','.'),4) AS source_4
 FROM clients

Unfortunately using the code above I get a table like this where all google aren't in column1, but get something like this:

 USERID  Vendors  (dobyr)  login  Source1  Source2  Source3  Age
 10bta   yes      1976     yes    google   hope     null     44
 25cwd   yes      1986     yes    hln      google   1045     34
 45tyj   no       1992     no     4345     hln      google   28
 645io   no       1960     no     null     null     google   30

Check out this [answer](https://stackoverflow.com/a/15108499/632537) to this [question](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns). — Isaac, Apr 04 '20 at 02:44

pwilcox · Accepted Answer · 2020-04-04T03:23:12.947

Below I use charindex to get the position of the first underscore. Then I use it again, to get the second underscore. It may help to know that the third parameter for charindex gives a starting position. From there, it's just a matter of making sure that you return null if the position is zero, and calibrating the substring to omit the underscores in the results.

select      userId, vendors, dobyr, login,
            source1 = iif(us1 = 0, null, substring(source, 1, us1 - 1)),
            source2 = iif(us1 = 0, null, substring(source, us1 + 1, len(source) - us2 - 1)),
            source3 = iif(us2 = 0, null, substring(source, us2 + 1, len(source) - us2 - 1)),
            age = datepart(year, getdate()) - dobyr
from        @clients c
cross apply (select us1 = charindex('_', source)) ap1
cross apply (select us2 = charindex('_', source, us1 + 1)) ap2

And I also went ahead and tried the xml approach by bvr as linked by Issac in the comments to your question. Though if you avoid the root-level tags it looks even simpler:

select      userId, vendors, dobyr, login,
            source1 = xml.value('src[1]', 'varchar(50)'),
            source2 = xml.value('src[2]', 'varchar(50)'),
            source3 = xml.value('src[3]', 'varchar(50)'),
            age = datepart(year, getdate()) - dobyr
from        @clients c
cross apply (select xml = convert(xml, 
                '<src>' + replace(source, '_', '</src><src>') + '</src>'
            )) ap;

I'm glad I tried it. I think in the end I prefer syntax for the latter, but I imagine the performance is worse.

score 1 · Answer 2 · answered Apr 04 '20 at 06:55

You could use

SELECT UserId, Vendors, dobyr, login, source,
       MAX(CASE WHEN RN = 1 THEN Value END) Source1,
       MAX(CASE WHEN RN = 2 THEN Value END) Source2,
       MAX(CASE WHEN RN = 3 THEN Value END) Source3,
       MAX(YEAR(GETDATE()) - dobyr) Age
FROM 
(
  VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'),
        ('25cwd', 'yes', 1986, 'yes', 'google_hln_1045'),
        ('45tyj', 'no', 1990, 'no', 'google_hln_4345'),
        ('645io', 'no', 1960, 'no', 'google')
) T(UserId, Vendors, dobyr, login, source)
CROSS APPLY 
(
  SELECT Value, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN
  FROM STRING_SPLIT(Source, '_') 
)SS
GROUP BY UserId, Vendors, dobyr, login, source;

Spit column that has underscore to multiple columns and also calculate age

2 Answers2