Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to split up text in a column

Author  Topic 

yoursurrogategod
Starting Member

14 Posts

Posted - 2012-11-16 : 09:25:10
Hi, I have this piece of text that is stored in our MS-SQL database (ignore the quotes and no, I can't redesign how this work specifically):
"TEST|00000298398293|EQ5|Patient"

Now, when I do a simple select, I get that result being returned. What I'd like to do is split that string based on the "|" character and return the individual strings associated with this string, so that I could have "TEST", "0000298398293", "EQ5" and "Patient" in different fields. How can I do this? In PHP, you can use the explode method, is there something like that in MS-SQL?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-16 : 09:46:40
DECLARE @Sql varchar(50) = 'TEST|00000298398293|EQ5|Patient'

This only works if you have 3 pipes


SELECT PARSENAME(REPLACE(@sql,'|','.'),4)
,PARSENAME(REPLACE(@sql,'|','.'),3)
,PARSENAME(REPLACE(@sql,'|','.'),2)
,PARSENAME(REPLACE(@sql,'|','.'),1)

Jim



Everyday I learn something that somebody else already knew
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2012-11-16 : 10:20:14
Awesome. That's a great solution to my problem. Thank you!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-16 : 10:20:56
You're Welcome.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2012-11-16 : 15:44:13
Hi again, this is the code that I have:
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
Attempts,
Subject,
PARSENAME(REPLACE(IDTag, '|', '.'), 3) AS Region,
PARSENAME(REPLACE(IDTag, '|', '.'), 2) AS PatientID,
PARSENAME(REPLACE(IDTag, '|', '.'), 1) AS FormType
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = 'Continuity of Care Forms TEST ZEND'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= 1
AND PARSENAME(REPLACE(IDTag, '|', '.'), 3) LIKE 'TEST'
order by CreationDate desc


How about doing a search on the region and select only the ones that are labeled as "TEST"? I thought originally SELECT INTO would work, but that wouldn't make sense. Would my code -- as I posted above -- do the trick?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-16 : 22:18:46
if its exact match you're looking at, you should use

PARSENAME(REPLACE(IDTag, '|', '.'), 3) = 'TEST'

if you want to look for pattern containing test you need to use

PARSENAME(REPLACE(IDTag, '|', '.'), 3) LIKE '% TEST%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-19 : 12:52:43
Hi, I'm back :) .

I got another weird problem. If the string is structured as such:
["IDTag"] => string(39) "ABC|000000099999999|lots-blah|"


This is my SQL:

SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
PARSENAME(REPLACE(IDTag, '|', '.'), 4) AS Region,
PARSENAME(REPLACE(IDTag, '|', '.'), 3) AS PatientID,
PARSENAME(REPLACE(IDTag, '|', '.'), 2) AS FormType,
PARSENAME(REPLACE(IDTag, '|', '.'), 1) AS NurseStation,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= ?
ORDER BY CreationDate desc

Region, PatientID, FormType and NurseStation are returned as NULL... is this due to the fact that there is an empty space after the last "|"?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 13:03:57
[code]
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[1]/text()') AS Region,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[2]/text()') AS PatientID,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[3]/text()') AS FormType,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[4]/text()') AS NurseStation,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= ?
ORDER BY CreationDate desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 13:05:44
if you dont want repeat it you can do CASTing part once by forming a derived table on join which links to table containing IDTag field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-19 : 13:40:33
quote:
Originally posted by visakh16

if you dont want repeat it you can do CASTing part once by forming a derived table on join which links to table containing IDTag field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Sorry, not sure what you mean. Very much a n00b :) .

I do have one additional question. How would I sort based on nurse station?

SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[1]/text()') AS Region,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[2]/text()') AS PatientID,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[3]/text()') AS FormType,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[4]/text()') AS NurseStation,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= ?
ORDER BY
CreationDate desc,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[4]/text()') AS NurseStation desc


When I do that, I get the following error message:
Incorrect syntax near the keyword 'AS'.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 14:21:59
I guess what Visakh meant is something like this where you do the casting once:
SELECT *,
xmlCol.query('Node[1]/text()') AS Region,
xmlCol.query('Node[2]/text()') AS PatientID,
xmlCol.query('Node[3]/text()') AS FormType,
xmlCol.query('Node[4]/text()') AS NurseStation
FROM
(
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml) AS xmlCol,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= 1
) s
ORDER BY
CreationDate desc,
NurseStation desc
The syntax error you are getting is because of the question mark. Usually you see question marks if you are writing a parameterized query - the intention being that the client replaces the question mark with a parameter value. If you are directly running it in SSMS, you should replace it yourself, for example as I have done above.
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-19 : 14:39:21
quote:
Originally posted by James K

I guess what Visakh meant is something like this where you do the casting once:
SELECT *,
xmlCol.query('Node[1]/text()') AS Region,
xmlCol.query('Node[2]/text()') AS PatientID,
xmlCol.query('Node[3]/text()') AS FormType,
xmlCol.query('Node[4]/text()') AS NurseStation
FROM
(
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml) AS xmlCol,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= 1
) s
ORDER BY
CreationDate desc,
NurseStation desc
The syntax error you are getting is because of the question mark. Usually you see question marks if you are writing a parameterized query - the intention being that the client replaces the question mark with a parameter value. If you are directly running it in SSMS, you should replace it yourself, for example as I have done above.


Hi, thanks for such a quick reply.

But in my case, I wasn't running it in SSMS, it was already in my web-app where the ? should have been replaced.

Also, after running you query in SQL Server Management Studio, I got this error (I replaced the inputs with relevant information):
Msg 305, Level 16, State 1, Line 1
The xml data type cannot be compared or sorted, except when using the IS NULL operator.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 14:57:32
[quote]Msg 305, Level 16, State 1, Line 1
The xml data type cannot be compared or sorted, except when using the IS NULL operator.
cast the order by to varchar or nvarchar as in
 ....
ORDER BY
CreationDate desc,
CAST(xmlCol.query('Node[4]/text()') AS NVARCHAR(256)) desc
Regarding the error that you were getting in the previous query, it is because of the aliasing in the order by clause. You should not alias in the order by clause
.... (Original query)
ORDER BY
CreationDate desc,
CAST(CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[4]/text()')
AS NVARCHAR(256)) AS NurseStation desc[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 01:31:09
quote:
Originally posted by James K

I guess what Visakh meant is something like this where you do the casting once:
SELECT *,
xmlCol.query('Node[1]/text()') AS Region,
xmlCol.query('Node[2]/text()') AS PatientID,
xmlCol.query('Node[3]/text()') AS FormType,
xmlCol.query('Node[4]/text()') AS NurseStation
FROM
(
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
FinalStatus,
Description,
RecipientIndex,
RecipientName,
Attempts,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml) AS xmlCol,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND DATEDIFF(dd, CreationDate, GETDATE()) <= 1
) s
ORDER BY
CreationDate desc,
NurseStation desc
The syntax error you are getting is because of the question mark. Usually you see question marks if you are writing a parameterized query - the intention being that the client replaces the question mark with a parameter value. If you are directly running it in SSMS, you should replace it yourself, for example as I have done above.


Indeed this is wht i meant
Didnt post it as I was not sure which table contains IDTag field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-21 : 15:13:26
Thanks James and Visakh.
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-21 : 15:32:35
lol, one more question.

I tried the below approach when comparing dates ($to and $from) and it worked, but what I'd like to know is if this is a good way to do things or are there cases that will trip me up in the future.
SELECT *,
xmlCol.query('Node[1]/text()') AS Region,
xmlCol.query('Node[2]/text()') AS PatientID,
xmlCol.query('Node[3]/text()') AS FormType,
xmlCol.query('Node[4]/text()') AS NurseStation
FROM
(
SELECT td.UniqueJobID,
FaxNumber,
CreationDate,
Description,
RecipientIndex,
RecipientName,
Subject,
CAST('<Node>' + REPLACE(IDTag,'|','</Node><Node>') + '</Node>' AS xml) AS xmlCol,
IDTag
FROM svcjobtracker.FAXCOMTxJob tj
INNER JOIN svcjobtracker.TxDetail td
ON tj.UniqueJobID = td.UniqueJobID
LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs
ON td.FinalStatus = fs.StatusValue
WHERE Subject = '$subjectTitle'
AND CreationDate <= '$to'
AND CreationDate >= '$from'
) s
ORDER BY
CAST(xmlCol.query('Node[4]/text()') AS NVARCHAR(256)) desc,
CreationDate desc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 16:27:19
In most cases, it is better to compare dates with a >= and <. For example, if you want to pick up all data for February 2013, you would do this:
WHERE
CreationDate >= '20130201' -- Feb 1st
AND CreationDate < '20130301' -- March 1st
This will correctly handle cases where there is a time portion to the CreationDate.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-21 : 22:19:19
see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-22 : 16:26:37
quote:
Originally posted by James K

In most cases, it is better to compare dates with a >= and <. For example, if you want to pick up all data for February 2013, you would do this:
WHERE
CreationDate >= '20130201' -- Feb 1st
AND CreationDate < '20130301' -- March 1st
This will correctly handle cases where there is a time portion to the CreationDate.


I figured as much. And thanks for pointing this out.
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-22 : 16:29:28
quote:
Originally posted by visakh16

see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




I'll read that now and give you feedback.

BTW, thanks for the help you guys. Based on what you've taught me, I was able to diagnose a problem with my query just now without having to bug you guys :) .
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts

Posted - 2013-02-22 : 16:54:29
visakh, just finished reading that article. Never knew of that point of view.

I do have a question, what does "Non Sargeable" mean?
Go to Top of Page
    Next Page

- Advertisement -