SQL SUBSTRING函数用来得到一个字符串的一部分。不同的数据库有不同的写法：
| SQL Server | SUBSTRING() |
| Oracle | SUBSTR() |
| MySQL | SUBSTR(), SUBSTRING() |
SQL SUBSTRING 语法
SUBSTRING (str, pos, len)
返回一个从pos开始，长度为len的字符串。
或者 (这个语法不被SQL Server支持):
SUBSTRING (str, pos)
返回一个从pos开始的剩余字符串。
SQL SUBSTRING 范例
数据表： Employees
| EmployeeId | FirstName | LastName | Department | Salary |
|---|---|---|---|---|
| 203 | Bikliaa | Fxoj | Finance | 78000 |
| 204 | Jozzh | Juheeo | Finance | 45800 |
| 205 | Syllauu | Dfaafk | Finance | 57000 |
| 206 | Gecrrcc | Srlkrt | Finance | 62000 |
| 302 | Uifaou | Bdnaa | Development | 75000 |
| 303 | Nhuiaa | Shillloa | Development | 55000 |
| 304 | Yhadd | Blooo | Development | 49000 |
选择所有的职员(employees)FirstName的第一个字母和Lastname：
SELECT SUBSTRING(FirstName, 1, 1) AS Initial, Lastname FROM Employees
结果会类似于：
| Initial | LastName |
|---|---|
| B | Fxoj |
| J | Juheeo |
| S | Dfaafk |
| G | Srlkrt |
| U | Bdnaa |
| N | Shillloa |
| Y | Blooo |
I know something needs to change with the
substring. I just do not know what it is. Basically, I need to put in
position 1 and 2, an 11.
UPDATE table
SET SUB_DMO = SUBSTRING(SUM_DMO,1,2) = ’11′
WHERE field 1 = ’000001688085′ AND
field2 = ‘TNT’ AND
field3 =’MTSILV08′
Substring is used for “Read”, not for “Write”. Say SUM_DMO is ’3456′, you want to change it to ’1156′, you can do like this: (this is mySQL syntax, if you use SQL Server or Oracle or others, the syntax may be different)
UPDATE table
SET SUB_DMO = CONCAT(’11′, SUBSTRING(SUM_DMO,3))
WHERE field 1 = ’000001688085′ AND
field2 = ‘TNT’ AND
field3 =’MTSILV08′
A right way to explain this statement with an example. But have some confusion with following statment :-
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo,
SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = ’1756′;
Hallo, Peter
I usually use sql statement via sql analyzer or query.
I think something missing in your sql statement, so I’m sure your statement won’t work.
Your statement is:
SET SUB_DMO = CONCAT(’11′, SUBSTRING(SUM_DMO,3))
It should be:
SET SUB_DMO = CONCAT(’11′, SUBSTRING(SUM_DMO,3,2))
However you are also able to use below statement (use plus sign (+)):
SET SUB_DMO = ’11′ + SUBSTRING(SUM_DMO,3,2)
I try the statement, and it works.
Printer Info recently posted..HP 4600dn Toner
It depends which database you use. As I said, it was mySQL syntax. If you use mySQL, you could use SUBSTRING(SUM_DMO,3) instead of SUBSTRING(SUM_DMO,3,2).