SQL:Example Uses of the SUBSTRING String Function

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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