| Author |
 Topic  |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
 Posted - 01/29/2013 : 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
India
47036 Posts |
Posted - 01/29/2013 : 08:39:18
|
will date part be consistent? ie all same datetime value?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/29/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/29/2013 : 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: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153458DECLARE @path varchaR(max) = '01-22-2013-12-58-18-#1.docx, 01-22-2013-12-58-18-#2.txt'
SELECT
','+Item
FROM
dbo.DelimitedSplit8K(REPLACE(@path,',','#'),'#') dsk
WHERE
ItemNumber%2 = 0
FOR 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. |
Edited by - James K on 01/29/2013 08:51:23 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 01/29/2013 : 08:50:42
|
so you want always part after # right?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/29/2013 : 08:51:34
|
quote:
Originally posted by visakh16
so you want always part after # right?
Yes visakh
--
Chandu |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/29/2013 : 08:54:32
|
quote:
Originally posted by James K
Hi James,
Thanks for your response
Am 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/29/2013 : 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. |
Edited by - James K on 01/29/2013 09:02:38 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/29/2013 : 09:06:33
|
James the format is MM-DD-YYYY-HH-MI-SS-#fileName.extension
Value of MM-DD-YYYY-HH-MI-SS will differ for each filename
--
Chandu |
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 01/29/2013 : 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
India
47036 Posts |
Posted - 01/29/2013 : 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 path1
Output
--------------------------------------------
path path1
----------------------------------------------
1.docx,2.txt 1.docx,2.txt,fajfbasj1.docx,righoiri.txt
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1396 Posts |
Posted - 01/29/2013 : 23:48:20
|
Thank you very much visakh......
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 01/29/2013 : 23:50:25
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
need this in single SELECT
