How to remove the first characters of a certain column in a table?

Question

In SQL, how can I delete the first 4 characters of the values ​​of a specific column in a table? The Student Code column name, and the approximate value is ABCD123Stu1231 . I want to remove the first 4 characters from the table for all records

I ask you to notify

Answer 1

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

Edit: To explain, RIGHT takes 2 arguments — the row (or column) to work and the number of characters returned (starting from the “right” row). 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.

Change again - I just read Andrew's answer, and he may very well intervene correctly, and I could be wrong. If this is the case (and you want to UPDATE the table, and not just return the results obtained using the method), you can do this:

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

He is on the right track, but his solution will contain 4 characters at the beginning of the line, instead of dropping the specified 4 characters.

Answer 2

 Stuff(someColumn, 1, 4, '') 

That says, starting at the first position of character 1 , replace 4 with characters with nothing. ''

Answer 3

Why use LEN so you have 2 string functions? All you need is a 5 symbol ...

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

Answer 4

Try the following:

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

Answer 5

Here is a simple layout of what you are 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 for the field you want to crop :)

The SUBSTRING function truncates code1, starting with the FIFTH character, and extends the length of CODE1 to less than 4 (the number of characters skipped at the beginning).

Answer 6

Full thing

 DECLARE @v varchar(10) SET @v='#temp' select STUFF(@v, 1, 1, '') WHERE LEFT(@v,1)='#' 

Answer 7

You can also do this in SQL ..

 substring(StudentCode,4,len(StudentCode)) 

syntax

 substring (ColumnName,<Number of starting Character which u want to remove>,<length of given string>) 

Answer 8

Try it. 100% working

 UPDATE Table_Name SET RIGHT(column_name, LEN(column_name) - 1) 

Answer 9

There is a built-in trim function that is ideal for this purpose.

 SELECT trim(both 'ag' from 'asdfg'); btrim ------- sdf (1 riga) 

http://www.postgresql.org/docs/8.1/static/functions-string.html

Answer 10

It would be nice to share, for DB2: INSERT(someColumn, 1, 4, '')

Stuff not supported in DB2

Answer 11

If you need to remove the first few characters preceded by a special character like # , this is a good option:

 UPDATE tblInvalidID SET [ColumnName] =stuff(ColumnName, 1, charindex('#', ColumnName), ' ') 

Answer 12

The top answer has unexpected behavior when the string length is less than expected, since passing negative values ​​to RIGHT truncates the first characters instead of the entire string. It makes sense to use RIGHT(MyColumn, -5) instead.

 create temp table foo (foo) as values ('123456789'),('12345678'),('1234567'),('123456'),('12345'),('1234'),('123'),('12'),('1'), (''); select foo, right(foo, length(foo) - 5), right(foo, -5) from foo; foo len(foo) - 5 just -5 --------- ------------ ------- 123456789 6789 6789 12345678 678 678 1234567 67 67 123456 6 6 12345 1234 234 123 3 12 1