CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Ray
Ray

Reputation: 319

sql - how do I remove half of the characters from a column?

I have an table column filled with usersIDs from a survey, but somehow the userID was written twice in each record. like below

UserID
========
35863586
12341234
321321

I've seen Aaron Alton's suggestion about removing certain characters, but in my case, I'll have to remove half of whatever the ID is to get it right because sometime the id is 8 digit sometimes 6 digits.

So this wouldn't help much, 

SELECT RIGHT(MyColumn, LEN(MyColumn) - 4) AS MyTrimmedColumn

Can someone suggested a way to solve my problem please?

Thanks very much

Upvotes: 1

Views: 1288

Answers (2)

Taicho
Taicho

Reputation: 143

update IDtable
set ID = LEFT(ID,(LEN(ID)/2))

This should update all of the columns with the correct string.

Upvotes: 1

John Woo
John Woo

Reputation: 263703

since you have mentioned that the length is 6 and sometimes 8,

divide the length by two

SELECT RIGHT(MyColumn, LEN(MyColumn) - (LEN(MyColumn) / 2)) AS MyTrimmedColumn

or simply

SELECT UserID, RIGHT(UserID, (LEN(UserID) / 2)) AS MyTrimmedColumn
FROM   table1

Upvotes: 8

Related Questions