SQL:Example Uses of the SUBSTRING String Function

最新推荐文章于 2020-10-06 20:42:22 发布
geovi
最新推荐文章于 2020-10-06 20:42:22 发布
阅读量659
点赞数
分类专栏：数据库编程
数据库编程专栏收录该内容
37 篇文章 0 订阅
订阅专栏
---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