Convert several practical SQL functions.

-- Convert money from Arabic numerals into large amounts
Create Function to_up (@ num numeric (14,2 ))
Returns varchar (100)
As
Begin
Declare @ ndata varchar (20), @ CDATA varchar (100)
Declare @ NSTR varchar (10), @ zflag bit, @ t varchar (10), @ I int
Set @ ndata = right (space (14) + Cast (cast (ABS (@ num * 100) as bigint) as varchar (20), 14)
Set @ CDATA =''
Set @ zflag = 0; Set @ I = 1

While @ I <= 14
Begin
Set @ NSTR = substring (@ ndata, @ I, 1)
If @ NSTR <>''
Begin
-- Number
Set @ NSTR = substring ('0, 1, 3, 3, 3, 4)
Set @ T = substring ('round round. ', @ I, 1)
If @ NSTR = '0'
Begin
If @ t in ('yi', 'quota', 'circle ')
Set @ CDATA = @ CDATA + @ t
If @ zflag = 0
Set @ zflag = 1
End
Else
Begin
If @ zflag = 1
Begin
Set @ CDATA = @ CDATA + '0' + @ NSTR + @ t

Set @ zflag = 0
End
Else
Set @ CDATA = @ CDATA + @ NSTR + @ t
End
End
Set @ I = @ I + 1
End
If @ num <0
Set @ CDATA = '(negative number)' + @ CDATA
If @ num = 0
Set @ CDATA = 'circle 0'
If @ NSTR = '0'
Set @ CDATA = @ CDATA + 'Integral'
Return (replace (@ CDATA, 'pick up', 'pick up '))
End

-- Converts a date to uppercase.
Create Function DBO. f_getupdata (@ date varchar (10 ))
Returns varchar (100)
Begin
Declare @ Table (Num int, value varchar (10 ))
Insert into @ table
Select 0, '0' Union
Select 1, '1' Union
Select 2, '2' Union
Select 3, '3' Union
Select 4, '4' Union
Select 5, '5' Union
Select 6, '6' Union
Select 7, '7' Union
Select 8, '8' Union
Select 9, '9'

Declare @ getdate varchar (100), @ month varchar (10), @ day varchar (10)
Set @ getdate = @ date
Select @ getdate = Replace (@ getdate, num, value)
From @ Table

Select @ getdate = stuff (@ getdate, charindex ('-', @ getdate), 1, 'Year'), charindex ('-', stuff (@ getdate, charindex ('-', @ getdate), 1, 'Year'), 1, 'month') + 'day'
Set @ month = substring (@ getdate, charindex ('Year', @ getdate) + 1, charindex ('month', @ getdate)-charindex ('Year', @ getdate) -1)
Set @ day = substring (@ getdate, charindex ('month', @ getdate) + 1, charindex ('day', @ getdate)-charindex ('month', @ getdate) -1)

Select @ getdate = case when (LEN (@ month) = 2 and left (@ month, 1) = '0') or Len (@ month) = 1 then left (@ getdate, 5) + right (@ month, 1) + 'month' + @ day + 'day'
When Len (@ month) = 2 and left (@ month, 1) = '1' and right (@ month, 1) = '0' then left (@ getdate, 5) + 'August 11' + @ day + 'day'
When Len (@ month) = 2 and left (@ month, 1) = '1' and right (@ month, 1) <> '0' then left (@ getdate, 5) + '10' + right (@ month, 1) + 'month' + @ day + 'day'
End

Set @ month = substring (@ getdate, charindex ('Year', @ getdate) + 1, charindex ('month', @ getdate)-charindex ('Year', @ getdate) -1)
Set @ day = substring (@ getdate, charindex ('month', @ getdate) + 1, charindex ('day', @ getdate)-charindex ('month', @ getdate) -1)

Select @ getdate = case
When (LEN (@ day) = 2 and left (@ Day, 1) = '0') or Len (@ day) = 1 then left (@ getdate, 5) + @ month + 'month' + right (@ Day, 1) + 'day'
When Len (@ day) = 2 and left (@ Day, 1) = '1' and right (@ Day, 1) = '0' then left (@ getdate, 5) + @ month + 'day 10'
When Len (@ day) = 2 and left (@ Day, 1) = '1' and right (@ Day, 1) <> '0' then left (@ getdate, 5) + @ month + 'Day of the Month' + right (@ Day, 1) + 'day'
When Len (@ day) = 2 and left (@ Day, 1) = '2' and right (@ Day, 1) = '0' then left (@ getdate, 5) + @ month + '20th of month'
When Len (@ day) = 2 and left (@ Day, 1) = '2' and right (@ Day, 1) <> '0' then left (@ getdate, 5) + @ month + 'two' + right (@ Day, 1) + 'day'
When Len (@ day) = 2 and left (@ Day, 1) = '3' and right (@ Day, 1) = '0' then left (@ getdate, 5) + @ month + 'Thirty days'
When Len (@ day) = 2 and left (@ Day, 1) = '3' and right (@ Day, 1) <> '0' then left (@ getdate, 5) + @ month + 'Thirty days' + right (@ Day, 1) + 'day'
End
Return @ getdate
End

-- Extract Chinese characters from the characters. I personally think it is very practical.
If object_id ('fnextractchina') is not null
Drop function fnextractchinese
Go
---- Create a string extraction Function
Create Function fnextractchinese (@ string varchar (100 ))
Returns varchar (100)
As
Begin
Declare @ STR varchar (100)
Set @ STR =''
While patindex ('% [seat] %', @ string) 0
Begin
Set @ STR = @ STR + substring (@ string, patindex ('% [seat] %', @ string), 1)
Set @ string = stuff (@ string, patindex ('% [A-seat] %', @ string), 1 ,'')
End
Return @ Str
End
Go
4. From region Creation
Create Function f_getpy (@ STR nvarchar (4000 ))
Returns nvarchar (4000)
As
Begin
Declare @ py table (
Ch char (1 ),
Hz1 nchar (1) Collate chinese_prc_cs_as_ks_ws,
Hz2 nchar (1) Collate chinese_prc_cs_as_ks_ws)
Insert @ py select 'A', n' A ', n' else'
Union all select 'B', n'8', n''
Union all select 'C', n'hangzhou', n'error'
Union all select 'D', N 'D', N 'region'
Union all select 'E', N 'hour', N 'er'
Union all select 'F', N 'F', N 'hangzhou'
Union all select 'G', n'hangzhou', n' over'
Union all select 'h', N 'haid', N 'hangzhou'
Union all select 'J', n' ', n'hangzhou'
Union all select 'k', N 'hangzhou', N 'quar'
Union all select 'l', n'hangzhou', n'hangzhou'
Union all select 'M', N 'M', N 'mu'
Union all select 'n', n' take ', N 'none'
Union all select 'O', n'ou', n' else'
Union all select 'P', n' lie ', n' exposure'
Union all select 'Q', N '7', N 'group'
Union all select 'R', N 'hangzhou', N 'hangzhou'
Union all select's ', N 'locks', N 'lock'
Union all select 'T', n' he ', n' else'
Union all select 'w', n'wa', n'hangzhou'
Union all select 'x', N 'Xi ', N 'taobao'
Union all select 'y', n' ya ', n' Yun'
Union all select 'Z', N 'Z', N 'do'
Declare @ I int
Set @ I = patindex ('% [Do-Do] %' collate chinese_prc_cs_as_ks_ws, @ Str)
While @ I> 0
Select @ STR = Replace (@ STR, substring (@ STR, @ I, 1), ch)
, @ I = patindex ('% [Do-Do] %' collate chinese_prc_cs_as_ks_ws, @ Str)
From @ py
Where substring (@ STR, @ I, 1) between Hz1 and hz2
Return (@ Str)
End
Go