|
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--取名字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 )
end
ENDSELECT
* 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
endelsebeginSET
@Name
= SUBSTRING(@Name, CHARINDEX(‘(‘, @Name)+1,len(@name)-CHARINDEX(‘(‘, @Name)-1)
end |