Create a function

1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5
6 -- ============================================== ========
7 -- Author: <ranbaoming>
8 -- Create date: <20110316>
9 -- Description: <obtain the function number based on the body number and the function number sent by the user>
10 -- =================================================== ========
11 alter function [dbo]. [get]
12 (
13 @ szGpsSn nvarchar (11)
14)
15 RETURNS nvarchar (80)
16
17 BEGIN
18 declare @ str varchar (8000)
19 declare @ str2 varchar (8000)
20 SELECT @ str = [Info] FROM [south]. [dbo]. [DetectInfo]
21 where [Info] LIKE '% IMEI %' AND [RTKSN] = @ szGpsSn
22 set @ str2 = right (@ str, 17)
23 RETURN @ str2
24 END

Split Functions

1 set ANSI_NULLS ON
2 set QUOTED_IDENTIFIER ON
3 go
4
5
6 ALTER function [dbo]. [Get_StrArrayStrOfIndex]
7 (
8 @ str varchar (1024), -- string to be split
9 @ split varchar (10), -- Separator
10 @ index int -- obtains the nth element.
11)
12 returns varchar (1024)
13
14 begin
15 declare @ location int
16 declare @ start int
17 declare @ next int
18 declare @ seed int
19
20 set @ str = ltrim (rtrim (@ str ))
21 set @ start = 1
22 set @ next = 1
23 set @ seed = len (@ split)
24
25 set @ location = charindex (@ split, @ str)
26 while @ location <> 0 and @ index> @ next
27 begin
28 set @ start = @ location + @ seed
29 set @ location = charindex (@ split, @ str, @ start)
30 set @ next = @ next + 1
31 end
32 if @ location = 0 select @ location = len (@ str) + 1
33 -- there are two situations: 1. The character string does not have a separator number 2. The character string contains a separator number. After jumping out of the while LOOP, @ location is 0, by default, there is a separator behind the string.
34
35 return substring (@ str, @ start, @ location-@ start)
36 end
37 -- call example: select dbo. Get_StrArrayStrOfIndex ('8, 9, 4', ',', 2)
38 -- Return Value: 9

Use of a function

1 declare @ SQL nvarchar (100)
2 set @ SQL = 'a detection faulty instrument B detection qualified instrument C Detection Instrument Number E warehouse receiving instrument F warehouse delivery instrument G settlement return instrument H return instrument I return repair instrument J special authorize instruments'
3 select substring (@ SQL, charindex ('A', @ SQL), 7)
4 select substring (@ SQL, charindex ('C', @ SQL), 7)
5 select substring (@ SQL, charindex ('J', @ SQL), 7)