SQL 如何删除表中特定列的第一个字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/982819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I remove the first characters of a specific column in a table?
提问by Shyju
In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Codeand an example value is ABCD123Stu1231.
I want to remove first 4 chars from my table for all records
在 SQL 中，如何删除表中特定列的值的前 4 个字符？列名是Student Code，示例值为ABCD123Stu1231。我想从我的表中删除所有记录的前 4 个字符
Please guide me
请指导我
回答by Aaron Alton
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".
编辑：解释一下，RIGHT 需要 2 个参数 - 要操作的字符串（或列），以及要返回的字符数（从字符串的“右侧”开始）。LEN 返回列数据的长度，我们减去 4，以便我们的 RIGHT 函数将最左边的 4 个字符留在“后面”。
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 keepthe 4 characters at the start of the string, rather than discarding said 4 characters.
他在正确的轨道上，但他的解决方案将保留字符串开头的 4 个字符，而不是丢弃所说的 4 个字符。
回答by AaronLS
Stuff(someColumn, 1, 4, '')
This says, starting with the first 1character position, replace 4characters with nothing ''
这表示，从第一个1字符位置开始，用空替换4字符''
回答by gbn
Why use LEN so you have 2 string functions? All you need is character 5 on...
为什么要使用 LEN 所以你有 2 个字符串函数？你所需要的只是角色 5...
...SUBSTRING (Code1, 5, 8000)...
回答by Andrew Hare
Try this:
尝试这个：
update table YourTable
set YourField = substring(YourField, 5, len(YourField)-3);
回答by Rob
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).
SUBSTRING 函数修剪 Code1，从第 5 个字符开始，一直到 CODE1 的长度减去 4（开始时跳过的字符数）。
回答by Higarian
The Complete thing
完整的东西
DECLARE @v varchar(10)
SET @v='#temp'
select STUFF(@v, 1, 1, '')
WHERE LEFT(@v,1)='#'
回答by Durgesh Pandey
You Can also do this in SQL..
您也可以在 SQL 中执行此操作..
substring(StudentCode,4,len(StudentCode))
syntax
句法
substring (ColumnName,<Number of starting Character which u want to remove>,<length of given string>)
回答by Durgesh Pandey
Try this. 100% working
尝试这个。100% 工作
UPDATE Table_Name
SET RIGHT(column_name, LEN(column_name) - 1)
回答by Noumenon
The top answer has some surprising behavior when the length of the string is less than expected, because passing negative values to RIGHT trims the first characters instead of the entire string. It makes more sense to just use RIGHT(MyColumn, -5)instead.
当字符串的长度小于预期时，最佳答案有一些令人惊讶的行为，因为将负值传递给 RIGHT 会修剪第一个字符而不是整个字符串。改为使用更有意义RIGHT(MyColumn, -5)。
An example comparing what you get when you use "length - 5" instead of "-5":
比较使用“length - 5”而不是“-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
回答by Superb Saif
It would be good to share, For DB2 use:
INSERT(someColumn, 1, 4, '')
最好分享一下，对于 DB2 使用：
INSERT(someColumn, 1, 4, '')
Stuffis not supported in DB2
StuffDB2 不支持
