SUBSTRING & PATINDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SUBSTRING & PATINDEX

Author

Topic

sqlteamForummer
Starting Member

13 Posts

Posted - 2005-11-30 : 13:29:16

I have to grab a substring from each item of the column: For ex
the column has 3 items: I have to grab werwe.exe from the 1st, wrkew.pdf from the 2nd and 872.gif from the 3rd. How should I do it?

Column1
--------
name\dexter.exe\city\234324
name\simpson.pdf\city\23423
name\qwe.gif\city\47384\45087
name\2325.txt\city\city
Thanx

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-30 : 13:37:48

You do know your explanation doesn't match your sample data at all

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sqlteamForummer
Starting Member

13 Posts

Posted - 2005-11-30 : 13:41:30

Oh Sorry,

I have to grab a substring from each item of the column: For ex
the column has 4 items: I have to grab dexter.exe from the 1st, simpson.pdf from the 2nd, qwe.gif from the 3rd and 2325.txt from the 4th. How should I do it?

Column1
--------
name\dexter.exe\city\234324
name\simpson.pdf\city\23423
name\qwe.gif\city\47384\45087
name\2325.txt\city\city
Thanx

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-30 : 14:04:30

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(255))
GO

INSERT INTO myTable99(Col1)
SELECT 'name\dexter.exe\city\234324' UNION ALL
SELECT 'name\simpson.pdf\city\23423' UNION ALL
SELECT 'name\qwe.gif\city\47384\45087' UNION ALL
SELECT 'name\2325.txt\city\city'
GO

SELECT SUBSTRING(Col1
, CHARINDEX('\',Col1)+1
, (CHARINDEX('\',Col1,CHARINDEX('\',Col1)+1)-1) - (CHARINDEX('\',Col1)+1))
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2005-11-30 : 14:11:23

This will split the source data into its 4 columns:


SELECT
  [COLUMN_1] = substring(Col1 + '\', 0 + 1, charindex('\', Col1 + '\', 0 + 1) - 0 - 1 )
, [COLUMN_2] = substring(Col1 + '\',  charindex('\', Col1 + '\') + 1, charindex('\', Col1 + '\',
	charindex('\', Col1 + '\') + 1) -  charindex('\', Col1 + '\') - 1 )
, [COLUMN_3] = substring(Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1, 
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1)
	-  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) - 1 )
, [COLUMN_4] = substring(Col1 + '\',  charindex('\', Col1 + '\',  
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) + 1, charindex('\', Col1 + '\',  
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) + 1) 
	-  charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) - 1 )
FROM	myTable99

Kristen

sqlteamForummer
Starting Member

13 Posts

Posted - 2005-11-30 : 14:18:54

Thanx much guys

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-30 : 15:04:34

quote:

Originally posted by Kristen

This will split the source data into its 4 columns:


SELECT
  [COLUMN_1] = substring(Col1 + '\', 0 + 1, charindex('\', Col1 + '\', 0 + 1) - 0 - 1 )
, [COLUMN_2] = substring(Col1 + '\',  charindex('\', Col1 + '\') + 1, charindex('\', Col1 + '\',
	charindex('\', Col1 + '\') + 1) -  charindex('\', Col1 + '\') - 1 )
, [COLUMN_3] = substring(Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1, 
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1)
	-  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) - 1 )
, [COLUMN_4] = substring(Col1 + '\',  charindex('\', Col1 + '\',  
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) + 1, charindex('\', Col1 + '\',  
	charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) + 1) 
	-  charindex('\', Col1 + '\',  charindex('\', Col1 + '\',  charindex('\', Col1 + '\') + 1) + 1) - 1 )
FROM	myTable99

Kristen

WTF?


  [COLUMN_1] = substring(Col1 + '\'  -- the string
			, 0 + 1 -- the starting position
			, charindex('\', Col1 + '\', 0 + 1) - 0 - 1 ) -- the offset


OK, I get the method behind the madness...

Place holders....