SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to split up text in a column		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

yoursurrogategod
Starting Member

14 Posts
Posted - 11/16/2012 :  09:25:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 11/16/2012 :  09:46:40  Show Profile  Reply with Quote
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 - 11/16/2012 :  10:20:14  Show Profile  Reply with Quote
Awesome. That's a great solution to my problem. Thank you!
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 11/16/2012 :  10:20:56  Show Profile  Reply with Quote
You're Welcome.

Jim

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

yoursurrogategod
Starting Member

14 Posts
Posted - 11/16/2012 :  15:44:13  Show Profile  Reply with Quote
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

India
47157 Posts
Posted - 11/16/2012 :  22:18:46  Show Profile  Reply with Quote
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 - 02/19/2013 :  12:52:43  Show Profile  Reply with Quote
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

India
47157 Posts
Posted - 02/19/2013 :  13:03:57  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 02/19/2013 :  13:05:44  Show Profile  Reply with Quote
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 - 02/19/2013 :  13:40:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1512 Posts
Posted - 02/19/2013 :  14:21:59  Show Profile  Reply with Quote
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 - 02/19/2013 :  14:39:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1512 Posts
Posted - 02/19/2013 :  14:57:32  Show Profile  Reply with Quote 
[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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 02/20/2013 :  01:31:09  Show Profile  Reply with Quote
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 - 02/21/2013 :  15:13:26  Show Profile  Reply with Quote
Thanks James and Visakh.
Go to Top of Page

yoursurrogategod
Starting Member

14 Posts
Posted - 02/21/2013 :  15:32:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1512 Posts
Posted - 02/21/2013 :  16:27:19  Show Profile  Reply with Quote
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

India
47157 Posts
Posted - 02/21/2013 :  22:19:19  Show Profile  Reply with Quote
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 - 02/22/2013 :  16:26:37  Show Profile  Reply with Quote
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 - 02/22/2013 :  16:29:28  Show Profile  Reply with Quote
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 - 02/22/2013 :  16:54:29  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000