How to use Trim in select queries for data in SQL tables-CodePudding

I have a table which stores data for AD users in one of columns like abc\sam, abc\bill, xyz\Ramos etc.. (domain\username)

I am trying to use select query to trim anything after\ and just give me domain with their location. That is, after trimming abc for example returns as NorthEast, xyz returns SouthPacfic etc.

Please suggest how can I write a SQL query for this

CodePudding user response：

You can use SUBSTRING to build your substring and CHARINDEX to find the
backslash's position:

SELECT SUBSTRING(column, CHARINDEX('\', column)   1, LEN(column)) FROM yourtable;

CodePudding user response：

your data

create  table test (c1 varchar(max));
insert into test values
('abc\sam'), 
('abc\bill'), 
('xyz\Ramos' );

you can use following queries to get your desired result using CHARINDEX and LEFT and RIGHT functions

select 
LEFT(c1, CHARINDEX('\', c1) - 1) location 
--,RIGHT(c1, CHARINDEX('\', c1) - 1) username
FROM Test

using CHARINDEX and SUBSTRING functions

select 
SUBSTRING (c1,0,CHARINDEX('\',c1)) location
--,SUBSTRING(c1,CHARINDEX('\',c1) 1,LEN(c1)) username
FROM Test

Using PARSENAME function.however this method can not work with more than three \ like (abc\sam\eee\fff)

Select 
PARSENAME(replace(c1,'\','.'),2) location 
--,parsename(replace(c1,'\','.'),1) username
FROM Test

dbfiddle