How do I remove the first characters of a specific

问题:

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

回答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.

回答2:

Stuff(someColumn, 1, 4, '')

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

回答3:

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

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

回答4:

Try this:

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

回答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).

回答6:

The Complete thing

DECLARE @v varchar(10)

SET @v='#temp'

select STUFF(@v, 1, 1, '')
WHERE LEFT(@v,1)='#'

回答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>)

回答8:

There's the built-in trim function that is perfect for the purpose.

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

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

回答9:

It would be good to share, For DB2 use: INSERT(someColumn, 1, 4, '')

Stuff is not supported in DB2

回答10:

Try this. 100% working

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

  

回答11:

If you have to remove the first few characters that are preceded by a special character like #, this is a good one:

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