How do I remove the first characters of a specific column in a table?

sql sql-server string tsql
Shyju picture Shyju · Jun 11, 2009 · Viewed 487.9k times · Source

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for all records

Please guide me

Answer

Aaron Alton picture Aaron Alton · Jun 11, 2009 
SELECT RIGHT(MyColumn, LEN(MyColumn) - 4) AS MyTrimmedColumn

Edit: To explain, RIGHT takes 2 arguments - the string (or column) to operate on, and the number of characters to return (starting at the "right" side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves the leftmost 4 characters "behind".

Hope this makes sense.

Edit again - I just read Andrew's response, and he may very well have interperpereted correctly, and I might be mistaken. If this is the case (and you want to UPDATE the table rather than just return doctored results), you can do this:

UPDATE MyTable
SET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4)

He's on the right track, but his solution will keep the 4 characters at the start of the string, rather than discarding said 4 characters.

Related questions

Better techniques for trimming leading zeros in SQL Server?

I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to …

Read More
IndexOf function in T-SQL

Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in T-SQL? Looking for something that returns the position of a substring within a string. in C# …

Read More
UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More