SQL:Example Uses of the SUBSTRING String Function

浏览数：17 / 时间：2015年06月12日

100

101

102

103

104

105

106

107

108

109

110

---Example Uses of the SUBSTRING String Function
--http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=4AB06421-E859-4B5F-A948-0C9640F3108D&tkw=sample-uses-of-the-substring-string-function
 
--取名字Usage #1 : Get the First Name and Last Name from a Full Name
 
DECLARE 
@FullName    VARCHAR(50) 
--set @FullName= ‘Mark Zuckerberg‘
set 
@FullName= ‘Geovin Du‘
SELECT 
SUBSTRING(@FullName, 1, CHARINDEX(‘ ‘, @FullName) - 1) AS 
[First 
Name],
       SUBSTRING(@FullName, CHARINDEX(‘ ‘, @FullName) + 1, LEN(@FullName)) AS 
[Last 
Name]
 
--取價格 Geovin Du
declare 
@s varchar(8000)
set 
@s=‘Item1(7RJ5401130-893)Item2(          -0)Item3(          -0)Item4(          -0)‘
 
SELECT 
SUBSTRING(@s,CHARINDEX(‘(‘, @s)+1,(CHARINDEX(‘-‘, @s)-CHARINDEX(‘(‘, @s))-1) as 
‘Item1 Name‘
SELECT 
SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1) as 
‘Item1 price‘
select 
@s=SUBSTRING(@s,CHARINDEX(‘)‘, @s)+1,len(@s)-CHARINDEX(‘)‘, @s)+1)
SELECT 
SUBSTRING(@s,CHARINDEX(‘(‘, @s)+1,(CHARINDEX(‘-‘, @s)-CHARINDEX(‘(‘, @s))-1) as 
‘Item2 Name‘
SELECT 
SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1) as 
‘Item2 price‘
select 
@s=SUBSTRING(@s,CHARINDEX(‘)‘, @s)+1,len(@s)-CHARINDEX(‘)‘, @s)+1)
SELECT 
SUBSTRING(@s,CHARINDEX(‘(‘, @s)+1,(CHARINDEX(‘-‘, @s)-CHARINDEX(‘(‘, @s))-1) as 
‘Item3 Name‘
SELECT 
SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1) as 
‘Item3 price‘
select 
@s=SUBSTRING(@s,CHARINDEX(‘)‘, @s)+1,len(@s)-CHARINDEX(‘)‘, @s)+1)
SELECT 
SUBSTRING(@s,CHARINDEX(‘(‘, @s)+1,(CHARINDEX(‘-‘, @s)-CHARINDEX(‘(‘, @s))-1) as 
‘Item4 Name‘
select 
@s=SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1)
select 
@s as 
‘Item4 price‘
--Item4(8BG4134215-2274)
 
declare 
@s varchar(8000)
set 
@s=‘Item1(8BG4157567-1522)Item2(8BG4154194-1536)Item3(8BG4158060-2135)Item4(8BG4134215-2274)‘
declare 
@name 
varchar(50),@value varchar(50)
DECLARE 
@Property TABLE 
(
    [Name]               VARCHAR(50),
    [Value]              VARCHAR(50)
)
while len(@s)>10
begin
     
    SELECT 
@name=SUBSTRING(@s,CHARINDEX(‘(‘, @s)+1,(CHARINDEX(‘-‘, @s)-CHARINDEX(‘(‘, @s))-1)
    SELECT 
@value=SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1) 
    select 
@s=SUBSTRING(@s,CHARINDEX(‘-‘, @s)+1,(CHARINDEX(‘)‘, @s)-CHARINDEX(‘-‘, @s))-1)   
    INSERT 
INTO 
@Property ( [Name], [Value] )
    VALUES 
( @Name, @Value )
end
 
SELECT 
* FROM 
@Property
 
 
--貨品編號和貨號 Item1(7RJ5401130-893)Item2(          -0)Item3(          -0)Item4(          -0)
--Item1(8BG4157567-1522)Item2(8BG4154194-1536)Item3(8BG4158060-2135)Item4(8BG4134215-2274)
DECLARE 
@NameValuePairs  VARCHAR(8000) 
set 
@NameValuePairs= ‘Item1(7RJ5401130-893)Item2(          -0)Item3(          -0)Item4(          -0)‘
DECLARE 
@NameValuePair   VARCHAR(100)
DECLARE 
@Name            
VARCHAR(50)
DECLARE 
@Value           VARCHAR(50)
DECLARE 
@Property TABLE 
(
    [Name]               VARCHAR(50),
    [Value]              VARCHAR(50)
)
while len(@NameValuePairs)>0
begin
  
 
    SET 
@NameValuePair = LEFT(@NameValuePairs, 
                              ISNULL(NULLIF(CHARINDEX(‘)‘, @NameValuePairs) - 1, 0),
                              LEN(@NameValuePairs)))
 
     print @NameValuePair
 
    SET 
@NameValuePairs = SUBSTRING(@NameValuePairs,
                                    ISNULL(NULLIF(CHARINDEX(‘)‘, @NameValuePairs), 0),
                                    LEN(@NameValuePairs)) + 1, LEN(@NameValuePairs))
 
    print @NameValuePairs
 
    SET 
@Name 
= SUBSTRING(@NameValuePair, 1, CHARINDEX(‘-‘, @NameValuePair) - 1)
   --判断为空
    if (len(@Name)-CHARINDEX(‘(‘, @Name)) =0   
    set 
@Name=‘‘
    else
    SET 
@Name 
= SUBSTRING(@Name, CHARINDEX(‘(‘, @Name)+1,len(@name)-CHARINDEX(‘(‘, @Name)-1)
     
    SET 
@Value = SUBSTRING(@NameValuePair, CHARINDEX(‘-‘, @NameValuePair) + 1, LEN(@NameValuePair))
    if @Name<>‘‘
    begin
    INSERT 
INTO 
@Property ( [Name], [Value] )
    VALUES 
( @Name, @Value )
    end
 
END
 
SELECT 
* FROM 
@Property
 
 
declare 
@Name 
varchar(200)
set 
@Name=‘Item2(          ‘
select 
CHARINDEX(‘(‘, @Name) as 
‘top‘
select 
len(@Name) as 
‘0‘
select 
len(@Name)-CHARINDEX(‘(‘, @Name) ‘len]‘
select 
@Name
 
if len(@Name)=(len(@Name)-CHARINDEX(‘(‘, @Name)-1)
begin
 select 
@Name
end
else
begin
SET 
@Name 
= SUBSTRING(@Name, CHARINDEX(‘(‘, @Name)+1,len(@name)-CHARINDEX(‘(‘, @Name)-1)
end