We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

Need to find substring in SQL

166 Views
Last Modified: 2017-03-29
I have the following data in a column

\\serverq\Q$\EXPORT0\20170131\FolderA\FolderA\0\Document123.tif
\\serverq\Q$\EXPORT1\20170131\FolderA\FolderA\0\Document345.tif
\\serverq\Q$\EXPORT2\20170131\FolderA\FolderA\0\Document567.tif
\\serverMain\Z$\EXPORT18\20160131\FolderA\FolderA\0\Document564.tif
\\serverMain\Z$\EXPORT19\20160131\FolderA\FolderA\0\Document434.tif
\\serverOffsite\W$\EXPORT100\20160131\FolderA\FolderA\0\Document324.tif
\\serverOffsite\W$\EXPORT102\20160131\FolderA\FolderA\0\Document324.tif

I need a TSQL to extract the sub string that is \EXPORT..\  

This is SQL Server 2005

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alexander ArdatovSoftware Manager

Commented:
I think it should be combination of PATINDEX and SUBSTRING functions
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Use SUBSTRING function together with CHARINDEX:
SELECT SUBSTRING(ColumnName,CHARINDEX('\EXPORT',ColumnName), LEN(ColumnName))

Open in new window

Tapan PattanaikSenior Engineer

Commented:
HI charlesbaldo,

SELECT SUBSTRING(ColumnName,CHARINDEX('$', ColumnName) + 2,LEN(ColumnName))   from TableName

OutPut:
EXPORT0\20170131\FolderA\FolderA\0\Document123.tif
EXPORT1\20170131\FolderA\FolderA\0\Document345.tif
EXPORT2\20170131\FolderA\FolderA\0\Document567.tif
EXPORT18\20160131\FolderA\FolderA\0\Document564.tif
EXPORT19\20160131\FolderA\FolderA\0\Document434.tif
EXPORT100\20160131\FolderA\FolderA\0\Document324.tif
EXPORT102\20160131\FolderA\FolderA\0\Document324.tif

Open in new window


Regards,
Tapan Pattanaik
Charles BaldoSoftware Developer

Author

Commented:
This was perfect, Thanks to others, but none had perfection likes yours
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.