Substring - 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

General SQL Server Forums

New to SQL Server Programming

Substring

Author

Topic

Steve95
Starting Member

19 Posts

Posted - 2007-03-19 : 11:59:00

Hi All

Iam new to sql, Iam using sql 2000 and have a field datatype varchar.

An example of the string: -

CN=Leighton Morgan/OU=WR/O=Extranet

I need to write a query that will allow me to extract the following:-

Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Please can you help

Many Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 12:04:30

Use this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

SELECT dbo.fnParseString(-1, '/', Col1) AS [Name],
dbo.fnParseString(-2, '/', Col1) AS [Company],
dbo.fnParseString(-3, '/', Col1) AS [Extranet]
from table1

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 12:08:40

I was going to post using the same fnParseString function but

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 12:38:48

Thank you.

The function is speedy since it does not do string manipulations. It only handles "pointers" to parts in string.

Peter Larsson
Helsingborg, Sweden

Steve95
Starting Member

19 Posts

Posted - 2007-03-20 : 05:51:07

Hi Peso

thanks for the above, I also trying to create a viewed inorder to limit that data. This what I have created so far for the view.

select *
from vw_god
WHERE ldate >= '2006-09-30%' and
(lRequest like '%eSharing/e-sharingforrfu.nsf%'
or lRequest like '%eSharing/e-sharingForElite.nsf%'
or lRequest like '%eSharing/e-sharingForWRU.nsf%') and
(source <> 'exclutions') and
(lauthenticateduser not like '%O=Ashridge%')

I want to put in this view the code

SELECT dbo.fnParseString(-1, '/', Col1) AS [Name],
dbo.fnParseString(-2, '/', Col1) AS [Company],
dbo.fnParseString(-3, '/', Col1) AS [Extranet]
from table1

Taking into consideration Iam trying to limit to around 5000 records from 5 millions please can advise best approach.

Many Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 05:56:20

Something like this

SELECT TOP 5000	dbo.fnParseString(-1, '/', Col1) AS [Name],
		dbo.fnParseString(-2, '/', Col1) AS [Company],
		dbo.fnParseString(-3, '/', Col1) AS [Extranet]
FROM		vw_God
WHERE		lDate >= '2006-09-30'
		AND (lRequest LIKE '%eSharing/e-sharingforrfu.nsf%'
			OR lRequest LIKE '%eSharing/e-sharingForElite.nsf%'
			OR lRequest LIKE '%eSharing/e-sharingForWRU.nsf%')
		AND Source <> 'exclutions'
		AND lAuthenticatedUser NOT LIKE '%O=Ashridge%'

You didn't tell us that the view vw_God is made of, so I just winged it.

Peter Larsson
Helsingborg, Sweden

Steve95
Starting Member

19 Posts

Posted - 2007-03-20 : 06:38:01

Hi Peso

once again thanks for the above and the speed data is coming is excellent.

However Iam still having problems as to how the data is coming back from the orginal request.

I have a field where data is represented as :-

CN=Leighton Morgan/OU=WR/O=Extranet

I need to extract the following from the above...
Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Currently Iam getting

Name: CN=Leighton Morgan
Company: OU=WR
and lastly the word appearing as O=Extranet

Sorry I did not let you know about the other information I am kind of getting my head around all this......tricky stuff.....

Many Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 07:02:12

[code]SELECT TOP 5000 SUBSTRING(dbo.fnParseString(-1, '/', Col1), 4, 8000) AS [Name],
SUBSTRING(dbo.fnParseString(-2, '/', Col1), 4, 8000) AS [Company],
SUBSTRING(dbo.fnParseString(-3, '/', Col1), 3, 8000) AS [Extranet]
FROM vw_God
WHERE lDate >= '2006-09-30'
AND (lRequest LIKE '%eSharing/e-sharingforrfu.nsf%'
OR lRequest LIKE '%eSharing/e-sharingForElite.nsf%'
OR lRequest LIKE '%eSharing/e-sharingForWRU.nsf%')
AND Source <> 'exclutions'
AND lAuthenticatedUser NOT LIKE '%O=Ashridge%'[/code]

Peter Larsson
Helsingborg, Sweden

Steve95
Starting Member

19 Posts

Posted - 2007-03-20 : 07:11:07

Hey Peso

you are excellent I wish had your sql knowledge.......

One final request I want the above syntax as a subquery so that I can also return all the other fields from the vw_god. Once that is completed I will make this query a new view.

Once again many thanks for all your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 07:14:32

Just add more lines like this

SUBSTRING(dbo.fnParseString(-x, '/', Col1), n, 8000) AS [xyz]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources