---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
SQL:Example Uses of the SUBSTRING String Function
最新推荐文章于 2024-09-05 09:00:00 发布