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

Question

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

Solution 1

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.

Solution 2

Stuff(someColumn, 1, 4, '')

This says, starting with the first 1 character position, replace 4 characters with nothing ''

Solution 3

Why use LEN so you have 2 string functions? All you need is character 5 on...

...SUBSTRING (Code1, 5, 8000)...

Solution 4

Try this:

update table YourTable
set YourField = substring(YourField, 5, len(YourField)-3);

Solution 5

Here's a simple mock-up of what you're trying to do :)

CREATE TABLE Codes
(
code1 varchar(10),
code2 varchar(10)
)

INSERT INTO Codes (CODE1, CODE2) vALUES ('ABCD1234','')


UPDATE Codes
SET code2 = SUBSTRING(Code1, 5, LEN(CODE1) -4)

So, use the last statement against the field you want to trim :)

The SUBSTRING function trims down Code1, starting at the FIFTH character, and continuing for the length of CODE1 less 4 (the number of characters skipped at the start).

View more solutions

Comments

[Scott Ivey]

this will fail for values with < 4 characters. You should add a case block to return the value of the column for < 4.

[Aaron Alton]

Likely the best way to handle it would simply be: UPDATE MyTableSET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4) WHERE LEN(MyColumn) > 4 The SUBSTRING wouldn't error out, but it would also unnecessarily "update" rows with fewer than four characters. That said, the OP indicated that they wanted to trim the first 4 characters of a specific column - I would assume unless provided with greater detail that ALL rows need to be trimmed.

[Aaron Alton]

@spencer7593 - hahaha...true. You could always add a WHERE clause to be safe: WHERE NOT ISNUMERIC(LEFT(MyColumn,1))

[Ian R. O'Brien]

Is it safe to call this on a NULL column? Would would happen in this case?

[Edwin Stoteler]

Old question I know, but I prefer this solution, since it doesn't use more then 1 function or have some random large number in it.

[Jeff]

Even more: if someColumn is something complex like a subselect from a CTE or somehting, this doesn't require evaluating it twice.

[Steven Ball]

FYI, this solution also works really well for trimming numeric values.

[CodeMonkey1313]

While this may be useful for PostgreSQL users, the OP specified tsql/sql-server in the question tags.

[sfarbota]

I ended up using COL_LENGTH('MyTable', 'MyColumn') instead of LEN(MyColumn) because in my case I wanted to exclude the first n characters no matter the size of the actual content within the column, but this worked well other than that!

[Developer Webs]

Is this future proof? Is it possible that future versions of MSSQL might use a max varchar size larger than 8,000?

[Gaspa79]

varchar(max) is already bigger than 8000 chars. If you put len(column) it'll work forever though

[ZygD]

Or just use 2000000000. Why add a LEN function. Adds no value and also ignores trailing spaces (if you want them)

[dcparham]

to augment what you said, here's a completed result. first, do a select, ensure ur getting the rows you want, then get that right in this Update. NOTE/beware - after it runs successfully do not run it again, for it indiscriminately cuts off first 4 chars:update table set textField = (SELECT STUFF(CONVERT(VARCHAR(MAX), textField), 1, 4, '')) where activitytype='A' and approveddate > '2017-06-30' and textField not like '%The County1%' and textField not like '%The County2%' and abstract not like '%The County3%') and activityid in (12345, ... range of id's ... 56789)

[nealmcb]

Note that e.g. in postgresql, the function is "LENGTH", not "LEN".

[user2366842]

Thanks, this worked well for me as a one-off thing. The accepted answer was for some reason chopping additional characters in some cases, but this works flawlessly.

[Spazmoose]

@user2366842 The likely reason why using the accepted answer was cutting off additional characters is that you probably had trailing spaces in your text. You can compensate for that if you want by using RTRIM & LTRIM.

[Noumenon]

This returns really weird results when the length is less than 4. See the example in my answer. RIGHT(MyColumn, -4) is more intuitive and shorter.

[Wassadamo]

Re-evaluating MyColumn twice is no good in many cases that call CTE's and sub-queries. See (answer below)[stackoverflow.com/a/12306661/5289570]

[palota]

What database do you use ? The question has tsql tag, but T-SQL does not allow negative parameters. I get error message "Invalid length parameter passed to the right function."

[palota]

It does not work if a value ends with a space. It returns '234 ' for value 'ABCD1234 '.

[Noumenon]

I didn't notice the tags. Split answer into T-SQL and Postgres sections. Thank you.

[Metafaniel]

For MySQL, is essentially the very same explanation except for the name of the function which is INSERT() instead of STUFF().