String manipulation question - 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

Transact-SQL (2000)

String manipulation question

Author

Topic

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 10:08:19

Hi Everyone,

I'm looking for some help on extracting specific data out of one particular table. This table holds a string of data that is pulled from Active Directory and represents a user's mail server:

/o=OLDSS/ou=HQ-NYC/cn=Configuration/cn=Servers/cn=NYCEX12/cn=Microsoft Private MDB

I'm looking to return only the text in BLUE, which can be any length but will always be between 'Servers/cn=' and '/cn=Microsoft Private MDB'. The values in RED can be anything of variable lengths (just to give you the background that the number of characters in RED will not always be the same every time).

I've been playing around with SUBSTRING and CHARINDEX but can't seem to get this to work. Any help would be greatly appreciated.

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 10:15:37

[code]DECLARE @Sample TABLE (Col1 VARCHAR(200))

INSERT @Sample
SELECT '/o=OLDSS/ou=HQ-NYC/cn=Configuration/cn=Servers/cn=NYCEX12/cn=Microsoft Private MDB'

SELECT Col1,
LEFT(RIGHT(Col1, p2), p2 - p1)
FROM (
SELECT Col1,
CHARINDEX('=nc/', REVERSE(Col1)) + 3 AS p1,
CHARINDEX('=nc/', REVERSE(Col1), 25) - 1 AS p2
FROM @Sample
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 10:23:57

Wow. That was fast!

It looks like you've returned exactly what I'm looking for in the second column of the returned results (only the text in BLUE) but the text in BLUE can be any server name of any length. How can I modify this so that I can return all of them (any only that column, I don't need/want the first one that contains the string)?

Thank you Peso!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 10:29:44

Change nothing!

DECLARE	@Sample TABLE (Col1 VARCHAR(200))

INSERT	@Sample
SELECT	'/o=OLDSS/ou=HQ-NYC/cn=Configuration/cn=Servers/cn=NYCEX12/cn=Microsoft Private MDB' UNION ALL
SELECT	'/o=blablabla/ou=wow!/cn=Configuration/cn=Servers/cn=Patron Saint of Lost Yaks/cn=Microsoft Private MDB'

SELECT	Col1,
	LEFT(RIGHT(Col1, p2), p2 - p1)
FROM	(
		SELECT	Col1,
			CHARINDEX('=nc/', REVERSE(Col1)) + 3 AS p1,
			CHARINDEX('=nc/', REVERSE(Col1), 25) - 1 AS p2
		FROM	@Sample
	) AS d

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 10:46:35

Hmm. If nothing is changed, I try the query as is and only get two results returned. The two results are based on the two SELECT statements under your INSERT. There are thousands of rows in this table, with each many unique strings in that one column (different text in RED and BLUE). Is there no way to return what I want without providing the exact string for each possibility?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 10:53:22

The first four lines of code is only to MIMIC your environment!
Since I have no access nor knowledge of your environment I have to make an environment.
This is the code you need!

SELECT	Col1,
	LEFT(RIGHT(Col1, p2), p2 - p1)
FROM	(
		SELECT	Col1,
			CHARINDEX('=nc/', REVERSE(Col1)) + 3 AS p1,
			CHARINDEX('=nc/', REVERSE(Col1), 25) - 1 AS p2
		FROM	{Your table name here}
	) AS d

And please replace my Col1 column name to the column name YOU use in YOUR environment.

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 11:09:19

Sorry, maybe I'm not explaining this well enough. I will try to be more precise.

If I run this query, I get over 6900 results returned:

SELECT ServerDN from UserConfig

Each of the results that are returned are strings that look like this:

/o=OLDSS/ou=HQ-NYC/cn=Configuration/cn=Servers/cn=NYCEX12/cn=Microsoft Private MDB

I would like everything stripped off of each string, except the text in BLUE. The query that you gave me was doing that but it was doing it based on calculations that you knew about each 'mimic' string. I will not know these details beforehand, I only know that the poisition of the data that I want will always be between 'Servers/cn=' and '/cn=Microsoft Private MDB'. If there was any way to simply retun what was between these two things, it would accomplish what I'm looking for.

Your first two queries were doing the right thing for the results returned in the second column but I won't know the details for the CHARINDEX everytime. I'm sorry but they are not doing exactly what I need yet. What am I missing? Is there more information that you need to understand what I need to do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 11:13:05

Oh please!

The two things being hard-wired here is the "25" part, since the wanted string cannot be of the last 25 characters.
Also "3" is hard-wired since we know the length of the search string!

SELECT	{Your column name here},
	LEFT(RIGHT({Your column name here}, p2), p2 - p1)
FROM	(
		SELECT	{Your column name here},
			CHARINDEX('=nc/', REVERSE({Your column name here})) + 3 AS p1,
			CHARINDEX('=nc/', REVERSE({Your column name here}), 25) - 1 AS p2
		FROM	{Your table name here}
	) AS d

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 12:34:07

By running this:

SELECT ServerDN,
LEFT(RIGHT(ServerDN, p2), p2 - p1)
FROM (
SELECT ServerDN,
CHARINDEX('=nc/', REVERSE(ServerDN) + 3 AS p1,
CHARINDEX('=nc/', REVERSE(ServerDN), 25) - 1 AS p2
FROM UserConfig
) AS d

I get this:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:38:30

You accidentally removed one extra paranthesis.

SELECT	ServerDN,
	LEFT(RIGHT(ServerDN, p2), p2 - p1)
FROM	(
		SELECT	ServerDN,
			CHARINDEX('=nc/', REVERSE(ServerDN)) + 3 AS p1,
			CHARINDEX('=nc/', REVERSE(ServerDN), 25) - 1 AS p2
		FROM	UserConfig
	) AS d

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 12:42:22

Oops. Thanks.

It still returns the following with that fixed, though:

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type ntext is invalid for argument 1 of reverse function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 12:46:23

And why are you using NTEXT as datatype?
Are you expecting more than 4000 characters in the ServerDN column?

If not, change NTEXT to NVARCHAR(4000)

E 12°55'05.25"
N 56°04'39.16"

BBernard
Starting Member

7 Posts

Posted - 2007-12-04 : 12:57:59

THANK YOU!!!

I get what I want now. I really appreciate all of your help on this!

To answer your previous question, the database that I'm dealing with is created by a third party vendor and that column has that data type set for it. Every person who uses that product would have the same setting for that column. I'm not sure why the vendor used that particular one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources