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 amp;tkw=sample-uses-of-the-substring-string-

---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 )

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 )

SELECT * FROM @Property


begin

SET @Name = SUBSTRING(@Name, CHARINDEX((, @Name)+1,len(@name)-CHARINDEX((, @Name)-1)

【Sql Server】基础之IF ELSE的使用，以及len和substring、convert函数的使用 如果有经常使用触发器或者写存储过程的同学，那么一定不会少用判断语句if_else 假如有一个需求，就是通过语句的方式添加一条记录，添加记录前需要判断当前文本的前60%的文本不能有所重复，其实就是判断某个字段文本不能重复的需求
String str = new String(“abc“)到底new了几个对象 如果常量池里面没有“abc”这个字符串，那虚拟机就会在堆内存中new出一个String对象，还会在常量池中new一个abc字符串对象；
mysql split string DROP PROCEDURE IF EXISTS SPLIT_STRING; DELIMITER // CREATE PROCEDURE SPLIT_STRING ( IN fullstr VARCHAR(1024), IN delim VARCHAR(1) SQL SECURI.
geovindu 读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.