Click here to Skip to main content
15,879,348 members
Search within:


Spit column that has underscore to multiple colums
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a table called clients and I'm trying to split the value  which contains underscore that is one column into multiple columns and I'm also trying to create a column that calculate 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     bing_hln_4345
645io      no        1960       no     bing


The goal is to have that look like this:
USERID    Vendors   (dobyr)   login    source1  Source2    source3     Age
10bta      yes       1976       yes     google    hope                 44
25cwd      yes       1986       yes     google    hln         1045     34
45tyj      no        1992       no      bing      hln         4345     28
645io      no        1960       no      bing                           30




What I have tried:

So, far, I was able to figure out how to calculate the age, but not parse the Source column:
select *, datepart(year, CURRENT_TIMESTAMP) - dobyr as Age
FROM clint
Posted
Updated 10-Apr-20 20:04pm
Add a Solution
Comments
j snooze 3-Apr-20 17:38pm    
You may want to check out
https://raresql.com/2013/01/10/sql-server-split-string-into-rows-based-on-multiple-delimiters/
MadMyche 3-Apr-20 18:55pm    
This article is based on an obsolete version of SQL Server- With the release of 2016
they added this in with the Split_String function.

4 solutions

SQL Server has a function which can split delineated text: Split_String().

I would recommend you read up on how to use it and go through the samples that MS has, not going to match exactly what you want but you will be able to work with it as a start
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
Posted
One of the way to achieve that is to use CTE[^]. See:

SQL
DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors  VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50))

INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source])
VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'),
('25cwd', 'yes', 1986, 'yes', 'google_hln_1045'),
('45tyj', 'no', 1990, 'no', 'bing_hln_4345'),
('645io', 'no', 1960, 'no', 'bing')


;WITH CTE AS
(
	--initial part
	SELECT 1 AS LoopNo, USERID, Vendors, dobyr,YEAR(GETDATE()) - dobyr AS Age, [login], LEFT([source], CHARINDEX('_', [source])-1) AS src, RIGHT([source], LEN([source])-CHARINDEX('_', [source])) AS Remainder
	FROM @tmp 
	WHERE CHARINDEX('_', [source]) > 0
	--recursive part
	UNION ALL
	SELECT LoopNo + 1 AS LoopNo, USERID, Vendors, dobyr, Age, [login], LEFT(Remainder, CHARINDEX('_', Remainder)-1) AS src, RIGHT(Remainder, LEN(Remainder)-CHARINDEX('_', Remainder)) AS Remainder
	FROM CTE
	WHERE CHARINDEX('_', Remainder) > 0
	UNION ALL
	SELECT LoopNo + 1 AS LoopNo, USERID, Vendors, dobyr, Age, [login], Remainder AS src, NULL AS Remainder
	FROM CTE
	WHERE CHARINDEX('_', Remainder) = 0
)
SELECT USERID, Vendors, dobyr, [login], Age, [1], [2], [3]
FROM 
(
	SELECT LoopNo, USERID, Vendors, dobyr, [login], Age, src
	FROM CTE 
	--ORDER BY USERID, LoopNo
) DT
PIVOT(MAX(src) FOR LoopNo IN([1], [2], [3])) PVT


Result:
USERID	Vendors	dobyr	login	Age	1	2	3
10bta	yes	1976	yes	44	google	hope	NULL
25cwd	yes	1986	yes	34	google	hln	1045
45tyj	no	1990	no	30	bing	hln	4345


For further details, please see:
SQL Server CTE Basics - Simple Talk[^]
SQL Server Common Table Expressions (CTE)[^]
 
Share this answer
 
Posted 
DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors  VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50))

INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source])
VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'),
('25cwd', 'yes', 1986, 'yes', 'google_hln_1045'),
('45tyj', 'no', 1990, 'no', 'bing_hl_445'),
('645io', 'no', 1960, 'no', 'bing')
  
 
SELECT USERID, Vendors, dobyr, [login],[source],
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN [source] ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))>=2 THEN SUBSTRING([source],1,(CHARINDEX('_',[source])-1)) 
      END 
	END AS source1,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,LEN([source])-CHARINDEX('_',[source])) ELSE
	  CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,CHARINDEX('_',[source],CHARINDEX('_',[source])+1)-CHARINDEX('_',[source])-1) ELSE ''
      END  END
	END AS source2,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source],CHARINDEX('_',[source])+1)+1,LEN([source])-CHARINDEX('_',[source],CHARINDEX('_',[source])+1)) ELSE ''
	  END  END
	END AS source3,
		Year(GETDATE())-dobyr AS Age
FROM @tmp
 
Share this answer
 
Posted 
DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors  VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50))

INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source])
VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'),
('25cwd', 'yes', 1986, 'yes', 'google_hln_1045'),
('45tyj', 'no', 1990, 'no', 'bing_hl_445'),
('645io', 'no', 1960, 'no', 'bing')
 
SELECT USERID, Vendors, dobyr, [login],[source],
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN [source] ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))>=2 THEN SUBSTRING([source],1,(CHARINDEX('_',[source])-1)) 
      END 
	END AS source1,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,LEN([source])-CHARINDEX('_',[source])) ELSE
	  CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,CHARINDEX('_',[source],CHARINDEX('_',[source])+1)-CHARINDEX('_',[source])-1) ELSE ''
      END  END
	END AS source2,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source],CHARINDEX('_',[source])+1)+1,LEN([source])-CHARINDEX('_',[source],CHARINDEX('_',[source])+1)) ELSE ''
	  END  END
	END AS source3,
		Year(GETDATE())-dobyr AS Age
FROM @tmp
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
how to sort multiple colum in grid view
How Do I Make My Combo Box With Multiple Colums
How to auto increment a value in a colum
how to merge to colums value in on column
Sql query to get data which has same id in multiple rows with different state
How to update multiple rows in one column inside datagridview
Spit PDF document into multiple documents and rename based on content in the PDF
Multiple values in a column
Populate a datagridview with a query that has dynamic columns
Referencing created colum

Advertise
Privacy
Cookies
Terms of Use
Last Updated 11 Apr 2020
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900