Using Sql to manipulate strings within a database field

[Josephii]

The database I am working with (SqlSvr 7.0) has a field which contains
a string of text, primarily a directory/folder path with filename (d:\dirpath1\subdirpath2\subdirpath3\filename.ext). I want to use SQL to extract the filename (filename.ext) from the end of the string and
place the result (filename and extension.. sometimes there may not be an extension) in say a new table. Help..

 

[foxdev]

This would be easy in a client tool such as VFP (and probably VB), but a bit tricky in SQLS since T-SQL doesn't have a "backwards" CharIndex function (a function that searches for a string, but does it from the right side).

I'm thinking you could use the REVERSE function, then apply CHARINDEX looking for the "\" character, which would be the last occurence of "\" since you already reversed it. Then you'll know how far from the right side the "\" appears, and can use SUBSTR to read the rest of the string.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Yes, use the REVERSE function, char_index to the first "\", then use Mid or Substring to extract it, then REVERSE it again and you are done...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[Josephii]

Thank you, both, FOXDEV & TOMSARK, for your knowledge and response.