SQL Replace 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 2005 Forums

Transact-SQL (2005)

SQL Replace Question

Author

Topic

Scoby
Starting Member

3 Posts

Posted - 2008-07-07 : 10:42:38

I'm trying to use a replace statement to strip away the extra info and just leave a persons name. This is what the info looks like.

CN=Somename Somename/OU=AAA/O=PDD
CN=Somename Somename/OU=AAA/O=PPP
CN=Somename Somename/OU=AAA/O=PPP

I have a replace statement that removes the CN= section of the information, but i can't get the replace statement to work with a wildcard for the '/OU=AAA/O=PDD' section.

This is the update statement i'm using.

UPDATE Table SET
ColName=LTRIM(RTRIM(REPLACE(REPLACE(ColName,'CN=',''),'/%','')))

It gives no errors but only replaces the CN= portion of the data. Do wildcards actually work with the replace statement or do i need another approach?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:58:58

[code]DECLARE @TABLE TABLE
(
data varchar(50)
)

INSERT INTO @TABLE
SELECT 'CN=Somename Somename/OU=AAA/O=PDD' UNION ALL
SELECT 'CN=Somename Somename/OU=AAA/O=PPP' UNION ALL
SELECT 'CN=Somename Somename/OU=AAA/O=PPP'

SELECT *, REPLACE(left(data, CHARINDEX('/', data) - 1), 'CN=', '')
FROM @TABLE

/*
data
-------------------------------------------------- -----------------
CN=Somename Somename/OU=AAA/O=PDD Somename Somename
CN=Somename Somename/OU=AAA/O=PPP Somename Somename
CN=Somename Somename/OU=AAA/O=PPP Somename Somename

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources