How to Split the column 'code'

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


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 |

Assuming the column name is col1,

	cv3location a
		SELECT * FROM dbo.DelimitedSplit8K(col1,'|')
	) b
	(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

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

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));
('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 |');

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

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

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'

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.

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.

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

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

	LEFT([code], CHARINDEX('|',[code])-1),

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.

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'

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

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.

So get your DBA to do it for you.