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.
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 pipesSELECT PARSENAME(REPLACE(@sql,'|','.'),4),PARSENAME(REPLACE(@sql,'|','.'),3),PARSENAME(REPLACE(@sql,'|','.'),2),PARSENAME(REPLACE(@sql,'|','.'),1)JimEveryday I learn something that somebody else already knew |
|
|
yoursurrogategod
Starting Member
14 Posts |
Posted - 2012-11-16 : 10:20:14
|
Awesome. That's a great solution to my problem. Thank you! |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 10:20:56
|
You're Welcome.JimEveryday I learn something that somebody else already knew |
|
|
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 FormTypeFROM svcjobtracker.FAXCOMTxJob tj INNER JOIN svcjobtracker.TxDetail td ON tj.UniqueJobID = td.UniqueJobID LEFT OUTER JOIN svcjobtracker.FAXCOMStatus fs ON td.FinalStatus = fs.StatusValueWHERE 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? |
|
|
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 usePARSENAME(REPLACE(IDTag, '|', '.'), 3) = 'TEST'if you want to look for pattern containing test you need to usePARSENAME(REPLACE(IDTag, '|', '.'), 3) LIKE '% TEST%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 "|"? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://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'. |
|
|
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 NurseStationFROM( 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. |
|
|
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 NurseStationFROM( 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 1The xml data type cannot be compared or sorted, except when using the IS NULL operator. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-19 : 14:57:32
|
[quote]Msg 305, Level 16, State 1, Line 1The 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] |
|
|
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 NurseStationFROM( 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 meantDidnt post it as I was not sure which table contains IDTag field ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
yoursurrogategod
Starting Member
14 Posts |
Posted - 2013-02-21 : 15:13:26
|
Thanks James and Visakh. |
|
|
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 NurseStationFROM( 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') sORDER BY CAST(xmlCol.query('Node[4]/text()') AS NVARCHAR(256)) desc, CreationDate desc |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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. |
|
|
yoursurrogategod
Starting Member
14 Posts |
|
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? |
|
|
Next Page
|
|
|
|
|