SQL截取字段字符串的方法 - Jason_davis

SQL截取字段字符串的方法

set @str=‘WX15-53-H-53-99-15-335-23’;

select @str as \'字符串\'

select len(@str) as \'字符长度\'

select charindex(\'-\',@str,1) as \'第一个逗号的索引值\'

select LEFT(@str,charindex(\'-\',@str,1)-1) as \'第一个值\'

select SUBSTRING(@str,charindex(\'-\',@str,1)+1,len(@str)) as \'从第一逗号开始截取出后面的字符串\'

select LEFT(SUBSTRING(@str,charindex(\'-\',@str,1)+1,len(@str)),CHARINDEX(\'-\',SUBSTRING(@str,charindex(\'-\',@str,1)+1,len(@str)),1)-1) as \'中间的值\'

select SUBSTRING(SUBSTRING(@str,charindex(\'-\',@str,1)+1,len(@str)),charindex(\'-\',SUBSTRING(@str,charindex(\'-\',@str,1)+1,len(@str)),1)+1,len(@str)) as \'第2个-后面的值’

 

select SUBSTRING(SUBSTRING(@str,charindex(\'-\',@str,2)+1,len(@str)),charindex(\'-\',SUBSTRING(@str,charindex(\'-\',@str,2)+1,len(@str)),2)+1,len(@str)) as \'第2个-后面的值’

select SUBSTRING(SUBSTRING(@str,charindex(\'-\',@str,6)+1,len(@str)),charindex(\'-\',SUBSTRING(@str,charindex(\'-\',@str,6)+1,len(@str)),6)+1,len(@str)) as \'第5个-后面的值’

以此类推 取出的值为：15-335-23