Click here to Skip to main content
15,921,577 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

So I have data which is held in one column and i want to split the data using delimiter of space. I have achieved this but I want to be able to select other data which I will not need to split into the results as a reporting.

For example,

Table 1: Employees details

Table 2: Payroll

Table 3: Departments

Now there is an ID that links all these tables which is the same. But i was to be able to select data from all these tables and also be able to do the splitting using the delimiter. Below is what I have done so far:

Here is the select statement I had before the split, now i want to split only column in one table but link all tables together and display results which i did using this statement:

SQL
select distinct f.fle_id,
				f.filename,
				c.clp_clip_name,
				f.FileExtension,
				c.locationsList, 
				f.UNC,
				f.Directory,
				f.framerate		 
from			lls_clips C with (NOLOCK)
inner join		lls_fileimports F with (NOLOCK)
on				c.fle_id = f.fle_id 
inner join		lls_jobs J with (NOLOCK)
on				c.fle_id = j.fle_id
where				J.LastUpdated >= dateadd(day, -1, getdate()) 


What I have tried:

SQL
declare @delimiter Varchar(50)

Set @delimiter = ' '
;WITH CTE As
(
	select 
			[LocationsList],
			[clp_clip_name],
			CAST('<m>' + Replace([LocationsList], @delimiter , '</m><m>') + '</m>' AS XML)
			AS [LocationsList XML]
		From [lls_clips]
)
Select	distinct

		[clp_clip_name],
		[locationsList], 		
		[LocationsList XML].value('/M[1]', 'varchar(50)') As [First Location],
		[LocationsList XML].value('/M[2]', 'varchar(50)') As [Second Location],
		[LocationsList XML].value('/M[3]', 'varchar(50)') As [Third Location],
		[LocationsList XML].value('/M[4]', 'varchar(50)') As [Fourth Location],
		[LocationsList XML].value('/M[5]', 'varchar(50)') As [Firth Location],
		[LocationsList XML].value('/M[6]', 'varchar(50)') As [Sixth Location]

From CTE
GO


******NOTES BELOW******

--Here are the other tables i want to link with [Files table - Files F] and [Jobs J]
--the fields from Files include - F.FL_ID, f.filename, f.uncpath, f.direcotry
--the fields from Jobs include J.FL_ID, J.filename
--the fields from the above table lls_clips include C.FL_ID, C.filename and so on

--So i need to be able to link all that data together in one report. The only column i am splitting is in one table which is LLS_Clips table
Posted
Updated 16-Feb-18 1:39am
v5
Comments
ZurdoDev 2-Mar-16 7:41am    
Where are you stuck. What is your question?
Geofferz 2-Mar-16 7:48am    
just updated the question, this is the select statement i had to return all data from the different tables without the splitting of columns:

select distinct f.fle_id,
f.filename,
c.clp_clip_name,
f.FileExtension,
c.locationsList,
f.UNC,
f.Directory,
f.framerate
from lls_clips C with (NOLOCK)
inner join lls_fileimports F with (NOLOCK)
on c.fle_id = f.fle_id
inner join lls_jobs J with (NOLOCK)
on c.fle_id = j.fle_id
where J.LastUpdated >= dateadd(day, -1, getdate())
John C Rayan 2-Mar-16 11:12am    
Can't you join them in the common table expression before getting the XML resultset?

1 solution

I have a column with fullname like 'david#jones' in table student.

so if you want to separate these like firstname as "david" and lastname as "jones"
here goes the query like

SELECT POSITION( '#' IN FULLNAME) AS POS ,SUBSTR(FULLNAME , 1,POS-1) AS FNAME,SUBSTR(FULLNAME,POS+1) AS LNAME
FROM STUDENT

The column POS identifies the postition of the special character,
the next sub string is taken form the first character to the last but one of the special character as FNAME and from the next position of the special character to the end is taken as LNAME.
 
Share this answer
 
Comments
CHill60 16-Feb-18 8:47am    
This only works if there is a single delimiter.

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



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