SQL:Example Uses of the SUBSTRING String Function

---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 NameDECLARE @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 Dudeclare @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)>10begin        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 )endSELECT * 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)>0begin     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 )    endENDSELECT * FROM @Propertydeclare @Name varchar(200)set @Name='Item2(          'select CHARINDEX('(', @Name) as 'top'select len(@Name) as '0'select len(@Name)-CHARINDEX('(', @Name) 'len]'select @Nameif len(@Name)=(len(@Name)-CHARINDEX('(', @Name)-1)begin select @NameendelsebeginSET @Name = SUBSTRING(@Name, CHARINDEX('(', @Name)+1,len(@name)-CHARINDEX('(', @Name)-1)end