SQL help

[ganesh21]

I need to get a part of a string in T-SQL.

Below is are two example of the string.

'\\path123\Imagename\abc123\date\filename.jpg'
'\\path567890\Imagename\abc123\date\filename.jpg'

the result i would like to get for each is:
path123
path567890

i try to use substring but sometime i get only part of the path.

 

[jmackay]

You can use regular expressions to get it, or something like "explode" from PHP...

eg:

$temp = explode("\", $pathvar);

$goodpath = temp[2];

or for the regular expression something like

pregmatch("/\\\\(.*?)\\/", $pathvar, $matches);
$goodpath = $matches[1];

I think, can't test the above atm but it should be close.. assuming you can do it in php or some other language.

 

[ganesh21]

You can use regular expressions to get it, or something like "explode" from PHP...

eg:

$temp = explode("\", $pathvar);

$goodpath = temp[2];

or for the regular expression something like

pregmatch("/\\\\(.*?)\\/", $pathvar, $matches);
$goodpath = $matches[1];

I think, can't test the above atm but it should be close.. assuming you can do it in php or some other language.

Thank you, but i have to do this in SQL only.

 

[jmackay]

Ah well good luck then

 

[mcravenufo]

Maybe this will help?

http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-49-instring.htm

 

[ganesh21]

Maybe this will help?

http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-49-instring.htm

thank you, that should work.

 

[mikeblas]

Let me know if you have any questions.

DECLARE @Foo AS VARCHAR(80)
DECLARE @Start AS INTEGER

SET @Foo = 'blahblah\\path123\Imagename\abc123\date\filename.jpg';
-- SET @FOO = '\\path567890\Imagename\abc123\date\filename.jpg';
-- SET @Foo = 'blahblahpath123Imagenameabc123datefilename.jpg';

SET @Start = PATINDEX('%\\%\%', @Foo);
IF @Start = 0 
	PRINT 'Not found';
ELSE
BEGIN
	DECLARE @Temp AS VARCHAR(80);
	DECLARE @End AS INT;
	SET @Temp = SUBSTRING(@Foo, @Start+2, LEN(@Foo) );
	SET @End = PATINDEX('%\%', @Temp);
	IF @End = 0
		PRINT 'Not found';
	ELSE
	BEGIN
		SET @Temp = SUBSTRING(@Temp, 0, @End );
		PRINT '''' + @Temp + '''';
	END
END

 

[ganesh21]

Let me know if you have any questions.

Thank you, it work.

 

[BlasterJaack]

Just because I'm on a sql kick, here is the answer in just a select to avoid using a proc. I tested with MSSQL not sure with Mysql. And it'll only work with UNC path's like the examples you gave..

SELECT SUBSTRING([NETWORK_PATH], 3, PATINDEX('%\%', RIGHT([NETWORK_PATH], LEN([NETWORK_PATH]) - 2)) -1)

 

[mikeblas]

Just because I'm on a sql kick, here is the answer in just a select to avoid using a proc. I tested with MSSQL not sure with Mysql. And it'll only work with UNC path's like the examples you gave..

This returns unpredictable results if the input is ill-formed. You'll want to shape it up for use in production.

 

[BlasterJaack]

Of course, but I was assuming he can trust the data, if he doesn't want to see the result he could easily just add a where clause,

WHERE [NETWORKPATH] LIKE '\\%\%'

if he wants to get an error for that row instead he could do

SELECT CASE 
	WHEN PATINDEX('\\%\%', [COLUMN]) = 1 THEN 
		SUBSTRING([COLUMN], 3, PATINDEX('%\%', RIGHT([COLUMN], LEN([COLUMN]) - 2)) -1)
	ELSE 'Yo this isn''t right' END

This will work for MSSQL I'm not sure about case statements in select statements in MySQL.

My way will also work for a table, instead of a proc for each individual UNC path. Unless you were suggesting a UDF.

And I'm not saying one way is better than the other, just been doing a lot of SQL lately and just throwing out different options.

 

[mikeblas]

Certainly, the SELECT-able statement is preferred!