Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 08:28:58
|
Solution please....DECLARE @path varchaR(max) = '01-22-2013-12-58-18-#1.docx, 01-22-2013-12-58-18-#2.txt'OUTPUT should be: 1.docx, 2.txt--Chandu |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 08:39:18
|
will date part be consistent? ie all same datetime value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 08:42:04
|
quote: Originally posted by visakh16 will date part be consistent? ie all same datetime value?
consistent date part is there and also there may any number of file names ('01-22-2013-12-58-18-#1.docx, 11-29-2013-12-58-18-#2.txt', '01-22-2013-12-60-18-#fajfbasj1.docx, 04-21-2012-12-58-18-#righoiri.txt')--Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 08:50:04
|
Until the day we have regular expressions in SQL Server, I can't see a way to do this using T-SQL string functions. Here is one way using a string splitter function from this thread: DECLARE" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458[code]DECLARE @path varchaR(max) = '01-22-2013-12-58-18-#1.docx, 01-22-2013-12-58-18-#2.txt'SELECT ','+ItemFROM dbo.DelimitedSplit8K(REPLACE(@path,',','#'),'#') dskWHERE ItemNumber%2 = 0FOR XML PATH('')Comment -- The function in that thread is only for VARCHAR(8000), so if you need varchar(max), another function that can handle varchar(max) would be required. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 08:50:42
|
so you want always part after # right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 08:51:34
|
quote: Originally posted by visakh16 so you want always part after # right?
Yes visakh--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 08:54:32
|
quote: Originally posted by James K
Hi James,Thanks for your responseAm trying to get this in single SELECT statement...If there is no solution at all, then i need to apply this method...--Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 09:01:07
|
As Visakh was leading to, if all the date parts are the same, no matter how many parts there are, you can use a single replace statement can't you?DECLARE @path varchaR(max) = '01-22-2013-12-58-18-#1.docx, 01-22-2013-12-58-18-#2.txt'SELECT REPLACE(@path,'01-22-2013-12-58-18-#','') Even if that date part is not known in advance, as long as they are all the same, or even a handful of known values, this can be extended to handle that. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 09:06:33
|
James the format is MM-DD-YYYY-HH-MI-SS-#fileName.extensionValue of MM-DD-YYYY-HH-MI-SS will differ for each filename--Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 09:21:46
|
I thought so, but was trying to see if you will fall for that :)I can think of some rather convoluted ways to accomplish this (which I won't post because I don't have them written, and I would be embarrassed to post them even if I had). Joining on the splitter function (or doing a cross apply to the result of the splitter function, or even making it a correlated column) seems most reliable and simple to me. That would still be one select statement, wouldn't it?Another possibility is to use a CLR UDF. That might even be faster and more efficient, but then again, it is a function just like the splitter function. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 09:42:32
|
this is my stab at it. Single select as you asked for!DECLARE @path varchaR(max) = '01-22-2013-12-58-18-#1.docx, 01-22-2013-12-58-18-#2.txt',@path1 varchar(max)='01-22-2013-12-58-18-#1.docx, 11-29-2013-12-58-18-#2.txt,01-22-2013-12-60-18-#fajfbasj1.docx, 04-21-2012-12-58-18-#righoiri.txt'SELECT REPLACE(CAST(CAST( '<Node><Row><Date>' + REPLACE(REPLACE(@path,'#','</Date><File>'),',','</File></Row><Row><Date>') + '</File></Row></Node>' AS xml).query('data(/Node/Row/File)') AS varchar(100)),' ',',') AS [path],REPLACE(CAST(CAST( '<Node><Row><Date>' + REPLACE(REPLACE(@path1,'#','</Date><File>'),',','</File></Row><Row><Date>') + '</File></Row></Node>' AS xml).query('data(/Node/Row/File)') AS varchar(100)),' ',',') AS path1Output--------------------------------------------path path1----------------------------------------------1.docx,2.txt 1.docx,2.txt,fajfbasj1.docx,righoiri.txt ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-29 : 23:48:20
|
Thank you very much visakh......--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 23:50:25
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|