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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site