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
This question is related to
sql
sql-server
string
tsql
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
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>)
The Complete thing
DECLARE @v varchar(10)
SET @v='#temp'
select STUFF(@v, 1, 1, '')
WHERE LEFT(@v,1)='#'
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), ' ')
Try this:
update table YourTable
set YourField = substring(YourField, 5, len(YourField)-3);
Try this. 100% working
UPDATE Table_Name
SET RIGHT(column_name, LEN(column_name) - 1)
Stuff(someColumn, 1, 4, '')
This says, starting with the first 1
character position, replace 4
characters with nothing ''
The top answer is not suitable when values may have length less than 4.
You will get "Invalid length parameter passed to the right function" because it doesn't accept negatives. Use a CASE statement:
SELECT case when len(foo) >= 4 then RIGHT(foo, LEN(foo) - 4) else '' end AS myfoo from mytable;
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 "length - 5" instead of "-5":
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
It would be good to share, For DB2 use:
INSERT(someColumn, 1, 4, '')
Stuff
is not supported in DB2
Why use LEN so you have 2 string functions? All you need is character 5 on...
...SUBSTRING (Code1, 5, 8000)...
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).
Source: Stackoverflow.com