SQL Server Performance

Parse Delimited String to get filename

Discussion in 'SQL Server 2005 General Developer Questions' started by smf, Jan 11, 2011.

  1. smf New Member

    I originally posted this on the new user forum but I think it should ask my questions here:
    Hi all, rookie here. I saw some old posts [02-05-2007 by sql_jr) that talk about being able to parse a a string in a column that is delimited by a '' character. In the replies is a reference to Nigel Rivett's function to return a table from a delimited string. http://www.mindsdoor.net/SQLTsql/ParseCSVString.html . If I had the following column, c12345 with the value "C:Documents and SettingssteveDesktopA-text-1.doc"(no quotes), How can I use the ParseCSVString.html or other code to get the filename, A-text-1.doc ?
    Thanks in advance!
  2. satya Moderator

  3. smf New Member

    <P mce_keep="true">Thank you for your reply.</P><P mce_keep="true">I was able to solve the problem using some sample code from the dbforums website.&nbsp; User Paul Young offered the following code the I used to config a solution:</P><P mce_keep="true">thanks and regards,</P><P mce_keep="true">Steve</P>Code:<BR>--------------------------------------------------------------------------------------------------<BR><A class=kLink id=KonaLink0 href="http://www.dbforums.com/#" target=undefined jQuery1294845352499="7"><FONT style="FONT-WEIGHT: 400; FONT-SIZE: 10pt; COLOR: blue! important; FONT-FAMILY: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; POSITION: static" color=blue><SPAN class=kLink style="FONT-WEIGHT: 400; FONT-SIZE: 10pt; COLOR: blue! important; BORDER-BOTTOM: blue 1px solid; FONT-FAMILY: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; POSITION: relative; BACKGROUND-COLOR: transparent">declare</SPAN></FONT><SPAN class=preLoadWrap id=preLoadWrap0 style="POSITION: relative"><FONT color=#283849> </FONT></SPAN><A class=kLink id=KonaLink0 href="http://www.dbforums.com/#" target=undefined jQuery1294845352499="7"><SPAN class=preLoadWrap id=preLoadWrap0 style="POSITION: relative"><DIV id=preLoadLayer0 style="DISPLAY: none; Z-INDEX: 2147482647; LEFT: -18px; POSITION: absolute; TOP: -22px"><FONT color=#283849><IMG class=preloadImg style="BORDER-RIGHT: medium none; BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 22px; BORDER-BOTTOM: medium none; HEIGHT: 22px" height=22 src="http://kona.kontera.com/javascript/lib/imgs/grey_loader.gif" width=22></FONT></DIV><P mce_keep="true"></SPAN></A>@s varchar(17)<BR>set @s = '1234|67890A|CDEFG'<BR>select @s as 'Original String'<BR>select reverse(@s) as 'In Reverse', datalength(@s) as 'String Length'<BR>select charindex('|',reverse(@s)) as 'Location of first ''|'' in Reverse String'<BR>select datalength(@s) - charindex('|',reverse(@s)) + 1 as 'Location of last ''|'' in Original string'<BR>--------------------------------------------------------------------------------------------------<!-- / message --><!-- sig --></P>
  4. Madhivanan Moderator

    declare @filename varchar(100)
    set @filename ='C:Documents and SettingssteveDesktopA-text-1.doc'
    select right(@filename,CHARINDEX('',reverse(@filename))-1)

Share This Page