CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Nemo
Nemo

Reputation: 1111

How to parse data in SQL Server 2008?

I have a column name called Username in my table in SQL Server 2008. Username has following data

Username
corp\abc
corp\petern
corp\timw

and so on

I need my Username to be

Username
abc
petern
timw

how do I parse the data in Username to skip corp\ ?

Thanks.

Upvotes: 0

Views: 8954

Answers (4)

user756519
user756519

Reputation:

You could use PARSENAME to split the data but the function uses dot (.) as the delimiter. So, you have to first replace the backward slashes (\) with period or dot (.).

Click here to view the demo in SQL Fiddle.

Script:

CREATE TABLE users
( 
    username VARCHAR(30) NOT NULL
);

INSERT INTO users (username) VALUES
     ('corp\abc'),
     ('corp\corporate\'),
     ('corp\yescorp'),
     ('corp\def'),
     ('\corpabc\'),
     ('corpabc\');

SELECT  username
    ,   PARSENAME(REPLACE(username, '\', '.'), 1) AS usernameonly
FROM    users;

Output:

USERNAME            USERNAMEONLY
-----------------   ------------
corp\abc            abc
corp\corporate\ 
corp\yescorp        yescorp
corp\def            def
\corpabc\   
corpabc\

Upvotes: 3

YvesR
YvesR

Reputation: 6222

You can get the username like this:

SELECT RIGHT('corp\username',LEN('corp\username')-PATINDEX('%\%','corp\username'))

=>

SELECT RIGHT(username,LEN(username)-PATINDEX('%\%',username))

This work on text, nvarchar(n - max)

Upvotes: 0

aaimnr
aaimnr

Reputation: 1646

Update dbo.Users
Set    Username = replace(Username, 'corp\\', '');

Please check the quoting beforehand, by first checking whether the results of select seem ok (in general it's a good practice with data updating statements):

select replace(Username, 'corp\\', '') from dbo.Users

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You might use following expression to extract username:

select substring (username, charindex ('\', username, 1) + 1, len(username))

If there is no backslash in username function will return entire username - practical if you mix sql server and Windows authentication.

Upvotes: 1

Related Questions