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

User · Answer

Try this  100  working  UPDATE Table Name SET RIGHT column name  LEN column name  - 1     nbsp  nbsp

User · Answer

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

User · Answer

The top answer is not suitable when values may have length less than 4  In T-SQL You will get  quot Invalid length parameter passed to the right function quot  because it doesn t accept negatives  Use a CASE statement  SELECT case when len foo   gt   4 then RIGHT foo  LEN foo  - 4  else    end AS myfoo from mytable   In Postgres Values less than 4 give the surprising behavior below instead of an error  because passing negative values to RIGHT trims the first characters instead of the entire string  It makes more sense to use RIGHT MyColumn  -5  instead  An example comparing what you get when you use the top answer s  quot length - 5 quot  instead of  quot -5 quot   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

User · Answer

Why use LEN so you have 2 string functions  All you need is character 5 on         SUBSTRING  Code1  5  8000

User · Answer

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

User · Answer

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

User · Answer

The Complete thing  DECLARE  v varchar 10   SET  v   temp   select STUFF  v  1  1      WHERE LEFT  v 1

User · Answer

Stuff someColumn  1  4        This says  starting with the first 1 character position  replace 4 characters with nothing

User · Answer

It would be good to share  For DB2 use  INSERT someColumn  1  4       Stuff is not supported in DB2

User · Answer

You Can also do this in SQL    substring StudentCode 4 len StudentCode     syntax  substring  ColumnName  lt Number of starting Character which u want to remove gt   lt length of given string gt

User · Answer

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

User · Answer

Try this   update table YourTable set YourField   substring YourField  5  len YourField -3

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

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to tsql