SQL Server extracts numbers, English and Chinese SQL statements, serversql

SQL Server extracts numbers, English and Chinese SQL statements, serversql

-- SQL checks whether the field value has the Chinese create function fun_getCN (@ str nvarchar (4000) returns nvarchar (4000) as begin declare @ word nchar (1), @ CN nvarchar (4000) set @ CN = ''while len (@ str)> 0 begin set @ word = left (@ str, 1) if unicode (@ word) between 19968 and 19968 + 20901 set @ CN = @ CN + @ word set @ str = right (@ str, len (@ str)-1) end return @ CN end select dbo. fun_getCN ('bucket Forum KDL ') -- Forum select dbo. fun_getCN ('asdkg KDL ') -- select dbo. fun_getCN ('asdkdl ') -- null ----------------------------------------- extract the number IF OBJECT_ID ('dbo. GET_NUMBER2 ') is not nulldrop function dbo. GET_NUMBER2GOCREATE function dbo. GET_NUMBER2 (@ s varchar (100) returns varchar (100) asbeginwhile patindex ('% [^ 0-9] %', @ S)> 0 BEGINset @ s = stuff (@ s, patindex ('% [^ 0-9] %', @ s), 1, '') ENDRETURN @ SENDGO -- test print dbo. GET_NUMBER ('Ha abc123abc') GO---123 ------------------------------------------------------------------ extract the English IF OBJECT_ID ('dbo. GET_STR ') is not nulldrop function dbo. GET_STRGOCREATE function dbo. GET_STR (@ s varchar (100) returns varchar (100) asbeginwhile patindex ('% [^ a-z] %', @ S)> 0 BEGINset @ s = stuff (@ s, patindex ('% [^ a-z] %', @ s), 1, '') ENDRETURN @ SENDGO -- test print dbo. GET_STR ('Ha abc123abc') GO -------------------------------------------------------------------- extract Chinese IF OBJECT_ID ('dbo. CHINA_STR ') is not nulldrop function dbo. CHINA_STRGOCREATE function dbo. CHINA_STR (@ s nvarchar (100) returns varchar (100) asbeginwhile patindex ('% [^ A-seat] %', @ S)> 0 SET @ S = STUFF (@ S, PATINDEX ('% [^ A-seat] %', @ S), 1, n') RETURN @ sendgoprint dbo. CHINA_STR ('Ha abc123abc') GO ---------------------------------------------------------------------- filter duplicate characters IF OBJECT_ID ('dbo. DISTINCT_STR ') is not nulldrop function dbo. DISTINCT_STRGOCREATE function dbo. DISTINCT_STR (@ s nvarchar (100), @ split varchar (50) returns varchar (100) ASBEGINIF @ s is null return (NULL) DECLARE @ new varchar (50 ), @ index int, @ temp varchar (50) if left (@ S, 1) <> @ SPLITSET @ S = @ SPLIT + @ sif right (@ S, 1) <> @ SPLITSET @ S = @ S + @ splitwhile charindex (@ SPLIT, @ S)> 0 and len (@ S) <> 1 BEGINSET @ INDEX = CHARINDEX (@ SPLIT, @ S) SET @ TEMP = LEFT (@ S, CHARINDEX (@ SPLIT, @ S, @ INDEX + LEN (@ SPLIT ))) IF @ new is null set @ NEW = ISNULL (@ NEW, '') + @ TEMPELSESET @ NEW = ISNULL (@ NEW,'') + REPLACE (@ TEMP, @ SPLIT, '') + @ splitwhile charindex (@ TEMP, @ S)> 0 BEGINSET @ S = STUFF (@ S, CHARINDEX (@ TEMP, @ S) + LEN (@ SPLIT ), CHARINDEX (@ SPLIT, @ S, CHARINDEX (@ TEMP, @ S) + LEN (@ SPLIT)-CHARINDEX (@ TEMP, @ S ),'') endendreturn right (LEFT (@ NEW, LEN (@ NEW)-1), LEN (LEFT (@ NEW, LEN (@ NEW)-1)-1) endgoprint dbo. DISTINCT_STR ('a, A, B, C, C, B, C, ') -- A, B, CGO unique filter duplicate characters 2IF OBJECT_ID ('dbo. DISTINCT_STR2 ') is not nulldrop function dbo. DISTINCT_STR2GOCREATE function dbo. DISTINCT_STR2 (@ S varchar (8000) returns varchar (100) ASBEGINIF @ s is null return (NULL) DECLARE @ new varchar (50), @ index int, @ temp varchar (50) while len (@ S)> 0 BEGINSET @ NEW = ISNULL (@ NEW, '') + LEFT (@ S, 1) SET @ S = REPLACE (@ S, LEFT (@ S, 1), '') ENDRETURN @ newendgoselect dbo. DISTINCT_STR2 ('aabccd ') -- ABCDGO--------------------------------------------------------------------IF OBJECT_ID ('dbo. SPLIT_STR ') is not nulldrop function dbo. SPLIT_STRGOCREATE function dbo. SPLIT_STR (@ S varchar (8000), -- string containing multiple data items @ INDEX int, -- location of the data item to be obtained @ SPLIT varchar (10) -- Data Separator) returns varchar (100) ASBEGINIF @ s is null return (NULL) DECLARE @ SPLITLEN intSELECT @ SPLITLEN = LEN (@ SPLIT + 'A ') -2 WHILE @ INDEX> 1 and charindex (@ SPLIT, @ S + @ SPLIT)> 0 SELECT @ INDEX = @ INDEX-1, @ S = STUFF (@ S, 1, CHARINDEX (@ SPLIT, @ S + @ SPLIT) + @ SPLITLEN, '') RETURN (ISNULL (LEFT (@ S, CHARINDEX (@ SPLIT, @ S + @ SPLIT) -1), '') endgoprint dbo. SPLIT_STR ('aa | BB | CC', 2, '|') -- GO

How to extract the numbers contained in a column from SQLServer

Use the PATINDEX function to locate the position where the number appears for the first time in a string.
Declare @ aa varchar (80), ---- INFO Column
@ Bb varchar (80)

Set @ aa = 'hello, 12'
Set @ bb =''
While PATINDEX ('% [0-9] %', @ aa) <> 0/* Find a number in each loop */
Begin
Set @ bb = @ bb + substring (@ aa, PATINDEX ('% [0-9] %', @ aa), 1) /* add the numbers */
Set @ aa = substring (@ aa, 1, PATINDEX ('% [0-9] %', @ aa)-1) +
Substring (@ aa, PATINDEX ('% [0-9] %', @ aa) + 1, len (@ aa) -PATINDEX ('% [0-9] %', @ aa)/* removes the obtained number from the original string, and find more numbers */
End
Select @ bb/* Expected result */

To query all records in a table, you can rewrite the preceding statement into a stored procedure and use a cursor for processing, insert the records to the corresponding virtual table to get the expected results.

In SQL server 2000, how does one use SQL statements to extract 100 pieces of data?

The TOP operator scans the output of the query (or subquery) and returns the first N rows it finds.
For example, it is assumed that 50 sales personnel may want to list the top three sales personnel in total sales volume for praise.
Select distinct top 3 total_sales FROM employee order by total_sales DESC