How to Split the column 'code'

Transact-SQL
1

This is the result of the following query, (one column) How can I extract the first two values. I need only ACH and Inpatient

select code from cv3location

code

ACH |.Inpatient |ACH1 |1021 |1 |
ACH |.Inpatient |ACH1 |1101 |
ACH |.Inpatient |ACH1 |1101 |1 |
ACH |.Inpatient |ACH1 |1102 |
ACH |.Inpatient |ACH1 |1102 |1 |
ACH |.Inpatient |ACH1 |1103 |
ACH |.Inpatient |ACH1 |1103 |1 |
ACH |.Inpatient |ACH1 |1104 |
ACH |.Inpatient |ACH1 |1104 |1 |
ACH |.Inpatient |ACH1 |1104 |2P |
ACH |.Inpatient |ACH1 |1105 |
ACH |.Inpatient |ACH1 |1105 |1 |
ACH |.Inpatient |ACH1 |1105 |2P |
ACH |.Inpatient |ACH1 |1106 |
ACH |.Inpatient |ACH1 |1106 |1 |
ACH |.Inpatient |ACH1 |1106 |2P |

2

Assuming the column name is col1,

SELECT *
FROM 
	cv3location a
	CROSS APPLY 
	(
		SELECT * FROM dbo.DelimitedSplit8K(col1,'|')
	) b
PIVOT
	(MAX(Item) FOR ItemNumber IN ([1],[2]))P

It uses a function called dbo.DelimitedSplit8K written by @JeffModen which you can download from this page.

1 Like
3

This is the 10th column and the split does not work

4

I didn't follow what you are saying? What is the 10th column?

Copy this code and see if it works for you. Start from there and adapt it to your tables/queries.

CREATE TABLE #tmp(col1 VARCHAR(256));
INSERT INTO #tmp VALUES
('ACH |.Inpatient |ACH1 |1021 |1 |'),
('ACH |.Inpatient |ACH1 |1101 |'),
('ACH |.Inpatient |ACH1 |1101 |1 |'),
('ACH |.Inpatient |ACH1 |1102 |'),
('ACH |.Inpatient |ACH1 |1102 |1 |'),
('ACH |.Inpatient |ACH1 |1103 |'),
('ACH |.Inpatient |ACH1 |1103 |1 |'),
('ACH |.Inpatient |ACH1 |1104 |'),
('ACH |.Inpatient |ACH1 |1104 |1 |'),
('ACH |.Inpatient |ACH1 |1104 |2P |'),
('ACH |.Inpatient |ACH1 |1105 |'),
('ACH |.Inpatient |ACH1 |1105 |1 |'),
('ACH |.Inpatient |ACH1 |1105 |2P |'),
('ACH |.Inpatient |ACH1 |1106 |'),
('ACH |.Inpatient |ACH1 |1106 |1 |'),
('ACH |.Inpatient |ACH1 |1106 |2P |');


SELECT *
FROM 
	#tmp a
	CROSS APPLY 
	(
		SELECT * FROM dbo.DelimitedSplit8K(col1,'|')
	) b
PIVOT
	(MAX(Item) FOR ItemNumber IN ([1],[2]))P;
	
DROP TABLE #tmp;
5

Thanks for your reply, But I am getting an error Invalid object name 'dbo.DelimitedSplit8K'.

6

This table has more than 30 columns, Column name 'CODE' is the 10th column. Which is the one I selected,. I need to split this and have two columns with values 'ACH' and 'inpatient' (there are more than 15000 rows in this table with values like 'RGH . . .. . .. etc , 'PLC .. . . etc'

7

You need to create the function dbo.DelimitedSplit8K in your database. Look in the link that I posted in my first reply, copy the code in Figure 21 of that article, open a query window in your database, paste it and run it. That will create the function. You need to do that only once.

8

Replace the "col1" in the first query I posted with [CODE].

If that does not work for you, post the table DDL and sample data - like the example with the #tmp table that I posted - which someone can copy and run.

9

Unfortunately I do not have the create function access :frowning: - So that wont work

10

The following code is somewhat confusing/convoluted, but it should work.

SELECT
	LEFT([code], CHARINDEX('|',[code])-1),
	LEFT
	(
		STUFF([code],1,CHARINDEX('|',[code]),''),
		CHARINDEX('|',STUFF([code],1,CHARINDEX('|',[code]),''))-1
	)
FROM
	cv3location
11

Thanks for your quick replies . . .Got an error -

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

12

Finally . . . .GOT IT .. A big Thank you

select substring(Code,1,3) Facility
,substring(Code,(CHARINDEX('|',Code,1)+2),9) PatientStatus
FROM [dbo].[CV3Location]
where substring(ltrim(code),1,3) = 'RGH'
and substring(Code,(CHARINDEX('|',Code,1)+2),9) = 'Inpatient'

13

This will give you wrong results if the Facility code is not exactly 3 characters or if the PatientStatus is not exactly 9 characters. So just check to make sure that all possible Facility codes are 3 characters long and all possible PatientStatus are 9 characters long.

1 Like
14

Thanks . . I will keep that in mind, I am still learning the DB here and yet to go back to the insert query . . Thanks for your quick replies.

16

So get your DBA to do it for you.