Shulou(Shulou.com)06/03 Report--

34. Convert a string to a hexadecimal function

/ * *

Convert the string to hexadecimal

Author: don't be idle

QQ: 75492895

Email: appleak46@yahoo.com.cn

* * /

-- create a function (suiyunonghen (not available)

Create Function VarCharToHex (@ Str Varchar)

Returns varchar (800)

As

Begin

Declare @ I int,@Asi int,@ModS int,@res varchar, @ Len int,@Cres varchar, @ tempstr varbinary

Select @ I = 1 Convert Convert (varbinary,@str)

While @ I 9) then Char (Ascii ('A') + @ Mods-10) + @ Cres else Cast (@ Mods as varchar (4)) + @ Cres end

@ Asi = @ Asi/16

End

Select @ res = @ res + @ Cres,@tempStr = substring (@ tempStr,2,@len-1), @ I = @ iTun1

End

Return @ res

End

Go

-- Test example

Select dbo.VarCharToHex ('Leaf')

-- run result

/ *

D2B6D7D3

, /

35. Remove the area code in front of multiple phone numbers with an area code in the field

-- original post address: http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx

-- Author: htl258 (Tony)

-- Date: 2010-04-28 23:22:15

-- Version:Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

-- Blog: http://blog.csdn.net/htl258

-- Subject: application example: remove the area code in front of multiple phone numbers with area code in the field

-- demand post: http://topic.csdn.net/u/20100428/20/f2572998-099c-463a-a530-707a40606c9c.html?53227

-- > generate test data table: tb

IF NOT OBJECT_ID ('[tb]') IS NULL

DROP TABLE [tb]

GO

CREATE TABLE [tb] ([name] NVARCHAR (10), [phone] NVARCHAR (40))

INSERT [tb]

SELECT'afewnedNoble 010-12345678 Universe 0571-86919111 'UNION ALL

SELECT'baud recorder 020-23950423Accord0756-34972654Compact 023-89732456'

GO

-- SELECT * FROM [tb]

-- > SQL query is as follows:

-1. Create a custom function

IF NOT OBJECT_ID ('[f_getphone]') IS NULL

DROP FUNCTION [f_getphone]

GO

CREATE FUNCTION f_getphone (@ s varchar)

RETURNS varchar (200)

AS

BEGIN

SET @ swarms since then

DECLARE @ r VARCHAR

WHILE CHARINDEX ('/', @ s) > 0

SELECT @ r=ISNULL (@ rations, races, pins, packs, etc.)

+ LEFT (STUFF (@ sMagne1) Charndex ('-', @ s),'')

, CHARINDEX ('/', @ s)-CHARINDEX ('-', @ s)-1)

, @ s=STUFF (@ spjor1, Charindex ('/', @ s),'')

RETURN @ r

END

GO

-- 2. Query

SELECT [name], dbo.f_getphone (phone) 'phone' FROM TB

/ *

Name phone

A 12345678/86919111

B 23950423/34972654/89732456

(2 rows affected)

, /

This article is from the CSDN blog.

-- reprint, please indicate the source:-- http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx

36. General functions for splitting SQL2000/2005 strings into lists

-- original post address: http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx

-

-- Author: htl258 (Tony)

-- Date: 2010-04-28 02:00:28

-- Version:Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:38

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

-- Blog: http://blog.csdn.net/htl258

-- Subject: SQL2000/2005 string split list general function

-

-- SQL2000/2005 string split list general function

IF OBJECT_ID ('fancigetstr') IS NOT NULL

DROP FUNCTION f_getstr

GO

CREATE FUNCTION f_getstr (

@ s NVARCHAR (4000),-- string to be split

@ flag NVARCHAR (10) =''- data delimiter

) RETURNS @ r TABLE (col NVARCHAR (1000))

AS

BEGIN

IF ISNULL (@ flag,'') =''AND LEN (ISNULL (@ flag,'') +' a') = 1

INSERT @ r

SELECT SUBSTRING (@ s _ # numbering 1 ~ 1)

FROM master..spt_values

WHERE TYPE='p' and number=@char collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id ASC) else 0 end) end-- Test sample select dbo.fun_getbh ('dawn')-- run results / * * /

43. SQL digit to English function

-- the posting address of brother Qingtian (qianjin036a):-- http://topic.csdn.net/u/20080614/12/d26adea8-ac05-4b06-8b8a-f46a4b564e3b.html-- number to English-- =-- Author: qianjin036a-- Create date:06/14/2008 02GV 27Rose 17muri-Description:Arabic numerals to English-- = go-- creation function CREATE FUNCTION Digit2English (@ arabia decimal (38jue 17)) RETURNS varchar (1000) ASBEGIN declare @ atoe table (an int E varchar (10) insert into @ atoe select 0Zero' union all select 1, union all select 2, two3, union all select 4, union all select 5, union all select 6, union all select 7, union all select 8, union all select 9, union all select 9, nine' declare @ integer bigint,@trillion int @ billion int,@million int,@thousand int,@hundred int,@english varchar (1000) select @ integer=@arabia,@english='' select @ trillion=@integer% 1000000000000000000500000000005000000000000 million=@integer% 1000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 @ integer% 100000000 @ integer% 1000000 @ hundred= (@ integer% 1000) if @ trillion > 0 set @ english=@english + dbo.ThreeDigit (@ trillion) + 'trillion' if @ billion > 0 set @ english=@english + dbo.ThreeDigit (@ billion) + 'billion' if @ million > 0 set @ english=@english + dbo.ThreeDigit (@ million) + 'million' if @ thousand > 0 set @ english=@english + dbo.ThreeDigit (@ thousand) + 'thousand' if @ hundred > 0 set @ english=@ English+ dbo.ThreeDigit (@ hundred) if @ english='' set @ english='zero'if @ arabia-@integer > 0.000000000 begin declare @ decimal decimal (18Magne17) select @ english=@english+'point' @ decimal=@arabia-@integer while @ decimal > 0.0 begin select @ english=@english+e+' 'from @ atoe where cast (@ decimal*10 as int) = a set @ decimal=@decimal*10-cast (@ decimal*10 as int) end end return @ englishENDGO-Author: qianjin036a-- Create date: 06 Dexter 02Description: ThreeDigit Arabic numerals to English-- = CREATE FUNCTION ThreeDigit (@ integer int) RETURNS varchar (100) WITH EXECUTE AS CALLERASBEGIN declare @ atoe table (an int E varchar (10)) insert into @ atoe select 0Zero' union all select 1, union all select 2, two3 union all select 3, union all select 4, union all select 5, union all select 6, union all select 7, union all select 8, eight9, union all select 9, nine10 Ten' union all select 11, union all select, union all select 14, union all select 15, union all select 16, union all select 17, union all select 18, union all select 19, union all select 20, union all select 20, union all select 30, union all select 40. 'forty' union all select 50 union all select 60 union all select' union all select 70 union all select 80 union all select 90 'ninety' declare @ english varchar set @ english='' if @ integer > 99 begin select @ english=e+' hundred' from @ atoe where @ integer=@integer% 100 if @ integer > 0 set @ english=@english+'and 'end if @ integer0 select @ english=@english+e+'' from @ atoe where @ integer=an if @ integer > 20 begin Select @ english=@english+e+' 'from @ atoe where @ integer/10*10=a set @ integer=@integer% 10 if @ integer > 0 select @ english=@english+e+'' from @ atoe where @ integer=an end RETURN @ englishENDGO select dbo.digit2english (123456789987654.321) union all select dbo.digit2english (120045080045054.8412) union all select dbo.digit2english (0.0102541) go/*- -one hundred and twenty three trillion four hundred and fifty six billion seven hundred and eighty nine million nine hundred and eighty seven thousand six hundred and fifty four point three two oneone hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one twozero point zero one zero two five four one*/

44. Full-width half-width conversion function

-- (the idea of this function refers to the powerful conversion function on CSDN)

-- Zou Jian 2005.01 (please keep this information for reference)-* /

Go

-- create function

Create function SBC2DBC

(

@ str nvarchar (4000),-- string to be converted

@ flag bit-conversion flag, 0 converted to half width, 1 converted to full width

)

Returns nvarchar (4000)

As

Begin

Declare @ pat nvarchar (8), @ step int,@i int,@spc int

If @ flag=0

Select @ pat=N'% [! -~]%', @ step=-65248,@str=replace (@ str,N'', N'')

Else

Select @ pat=N'% [!-~]%', @ step=65248,@str=replace (@ str,N'', N'')

Set @ i=patindex (@ pat collate latin1_general_bin,@str)

While @ I > 0

Select @ str=replace (@ str,substring (@ str,@i,1), nchar (unicode (substring (@ str,@i,1)) + @ step))

, @ i=patindex (@ pat collate latin1_general_bin,@str)

Return (@ str)

End

-- Test example

Select dbo.SBC2DBC ('~ causal baccalaure1)

-- run result

/ *

~ ca! B

, /

-- full width table with half width

/ *

ASCII full-width character Unicode half-width character Unicode

0x20 "" blanks Ubun3000 "" blanks Ubun0020

0x21! U+ff01! Utility 0021

0x22 "U+ff02" Ubuntu 0022

0x23 # U+ff03 # Utility 0023

0x24 $U+ff04 $Utility 0024

0x25 U+ff05 Utility 0025

0x26 & U+ff06 & Ubuntu 0026

0x27 'U+ff07' Ubuntu 0027

0x28 (U+ff08 (Utility 0028)

0x29) U+ff09) Utility 0029

0x2a * U+ff0a * Ubun002a

0x2b + U+ff0b + Ubun002b

0x2c, U+ff0c, Utility 002c

0x2d-U+ff0d-Utility 002d

0x2e. U + ff0e. Utt002e

0x2f / U+ff0f / Ubun002f

0x30 0U+ff10 0U + 0030

0x31 1U+ff11 1U + 0031

0x32 2U+ff12 2U + 0032

0x33 3U+ff13 3U + 0033

0x34 4U+ff14 4U + 0034

0x35 5U+ff15 5U + 0035

0x36 6U+ff16 6U + 0036

0x37 7U+ff17 7U + 0037

0x38 8U+ff18 8U + 0038

0x39 9U+ff19 9U + 0039

0x3a: U+ff1a: Ubun003a

0x3b; U+ff1b; Ubun003b

0x3c < U+ff1c

< U+003c 0x3d ＝U+ff1d = U+003d 0x3e ＞U+ff1e >

Utt003e

0x3f? U+ff1f? Upright 003f

0x40 @ U+ff20 @ Ubun0040

0x41 AU+ff21 AU+ 0041

0x42 BU+ff22 BU+ 0042

0x43 CU+ff23 CU+ 0043

0x44 DU+ff24 DU+ 0044

0x45 EU+ff25 EU+ 0045

0x46 FU+ff26 FU+ 0046

0x47 GU+ff27 GU+ 0047

0x48 HU+ff28 HU+ 0048

0x49 IU+ff29 IU+ 0049

0x4a JU+ff2a JU+ 004a

0x4b KU+ff2b KU+ 004b

0x4c LU+ff2c LU+ 004c

0x4d MU+ff2d MU+ 004d

0x4e NU+ff2e NU+ 004e

0x4f OU+ff2f OU+ 004f

0x50 PU+ff30 PU+ 0050

0x51 QU+ff31 QU+ 0051

0x52 RU+ff32 RU+ 0052

0x53 SU+ff33 SU+ 0053

0x54 TU+ff34 TU+ 0054

0x55 UU+ff35 UU+ 0055

0x56 VU+ff36 VU+ 0056

0x57 WU+ff37 WU+ 0057

0x58 XU+ff38 XU+ 0058

0x59 YU+ff39 YU+ 0059

0x5a ZU+ff3a ZU+ 005a

0x5b [U+ff3b [Ubun005b]

0x5c\ U+ff3c / Ubun005c

0x5d] U+ff3d] Utt005d

0x5e ^ U+ff3e ^ Utility 005e

0x5f _ U+ff3f _ Ubun005f

0x60 'U+ff40 `Upright 0060

0x61 aU+ff41 aU+ 0061

0x62 bU+ff42 bU+ 0062

0x63 cU+ff43 cU+ 0063

0x64 dU+ff44 dU+ 0064

0x65 eU+ff45 eU+ 0065

0x66 fU+ff46 fU+ 0066

0x67 gU+ff47 gU+ 0067

0x68 hU+ff48 hU+ 0068

0x69 iU+ff49 iU+ 0069

0x6a jU+ff4a jU+ 006a

0x6b kU+ff4b kU+ 006b

0x6c lU+ff4c lU+ 006c

0x6d mU+ff4d mU+ 006d

0x6e nU+ff4e nU+ 006e

0x6f oU+ff4f oU+ 006f

0x70 pU+ff50 pU+ 0070

0x71 qU+ff51 qU+ 0071

0x72 rU+ff52 rU+ 0072

0x73 sU+ff53 sU+ 0073

0x74 tU+ff54 tU+ 0074

0x75 uU+ff55 uU+ 0075

0x76 vU+ff56 vU+ 0076

0x77 wU+ff57 wU+ 0077

0x78 xU+ff58 xU+ 0078

0x79 yU+ff59 yU+ 0079

0x7a zU+ff5a zU+ 007a

0x7b {U+ff5b {Ubun007b

0x7c | U+ff5c | Utility 007c

0x7d} U+ff5d} Upright 007d

0x7e ~ U+ff5e ~ Ubun007e

, /

45. Returns a random time in two time ranges

/ * * function name: RandDateTime* function: returns a random time within two time ranges * Author: lan Xigang * Date: 2009-11-30 minutes. Create the function create Function RandDateTime (@ RandNum Decimal (38pc18) Random values for 0-1 time suggest Rand () @ StartTime DateTime,-- first time @ EndTime DateTime-- second time) Returns DateTimeAsBegin Declare @ HourDiff Decimal (38Power18)-- hourly difference between two times Declare @ MsPartDiff Decimal (382.18)-- difference of millisecond part Declare @ SmallDate DateTime Declare @ ReturnDateTime DateTime / * take the difference of each part * / Set @ HourDiff = DateDiff (hh,@StartTime) @ EndTime) Set @ MsPartDiff = Abs (DateDiff (ms,DateAdd (hh,@HourDiff,@StartTime), @ EndTime) Select @ SmallDate= (Case When @ HourDiff > 0 Then @ StartTime Else @ EndTime End)-- take a shorter time Set @ HourDiff = Abs (@ HourDiff) ActionLable: Declare @ HourDecimal Decimal (38 Magazine 18)-- the fractional part of the hour Declare @ HourString varchar (200) Set @ HourDiff = @ HourDiff * @ RandNum Set @ HourString = CONVERT @ HourDiff) Set @ HourString = SubString (@ HourString,CharIndex ('.', @ HourString) + 1 Len (@ HourString)) Set @ HourString = '0.' + @ HourString Set @ HourDecimal = Convert (Decimal (38) @ HourString)-get the decimal part of the hour Set @ MsPartDiff = (@ MsPartDiff + @ HourDecimal * 3600mm 1000) * @ RandNum / * millisecond difference because the previous @ MsPartDiff is the millisecond difference after two hours @ HourDecimal * 360000001000 the millisecond difference of the decimal part of the hour will not be greater than the hour millisecond will not overflow * / Set @ ReturnDateTime = DateAdd (hh,@HourDiff @ SmallDate) Set @ ReturnDateTime = DateAdd (ms,@MsPartDiff,@ReturnDateTime) Return @ ReturnDateTimeEnd-- Test sample select dbo.RandDateTime (Rand (), '2011-03-21 00 Groupe 00.000' '2011-03-21 23 affected 59 affected 00.000') go 10-- Operation results / *-- 2011-03-21 16 row 44 affected 58.990 (1 row (s) affected)-2011-03-21 0000 affected 33.313 (1 row (s))- -2011-03-21 15 row (s) affected)-2011-03-21 15 row (s) affected)-- 2011-03-21 15 row 11 51.047 (1 row ( S) affected)-2011-03-21 14 row 39 affected 23.597 (1 row (s) affected)-2011-03-21 07 24 row (s) affected)-2011-03- 21 06 row 15 affected 49.653 (1 row (s) affected)-2011-03-21 02 14. 757 (1 row (s) affected)-2011-03-21 10 row (s) 14. 370 (1 row (s)) * /

46. The function of getting the number of elements

Go

-- create function (author: csdn Zou Jian)

Create function getstrarrlength (@ str varchar (8000))

Returns int

As

Begin

Declare @ int_return int

Declare @ start int

Declare @ next int

Declare @ location int

Select @ str =','+ @ str +','

Select @ str=replace (@ str,',')

Select @ start = 1

Select @ next = 1

Select @ location = charindex (',', @ str,@start)

While (@ location 0)

Begin

Select @ start = @ location + 1

Select @ location = charindex (',', @ str,@start)

Select @ next = @ next + 1

End

Select @ int_return = @ next-2

Return @ int_return

End

-- Test example

SELECT [dbo]. [getstrarrlength] ('1Jing 2 Jing 3 Ji 4 a recorder breco crecy d')

-- run result

/ *

, /

/ *

Description:

I started to think about looking directly at the number of commas, replacing commas with replace, and finding the length difference, but here this function is handled by two commas adjacent to each other.

, /

47. A function that gets the value of a specified index

Go

-- create function (author: csdn Zou Jian)

Create function getstrofindex (@ str varchar (8000), @ index int = 0)

Returns varchar (8000)

As

Begin

Declare @ str_return varchar 8000

Declare @ start int

Declare @ next int

Declare @ location int

Select @ start = 1

Select @ next = 1-if the habit starts, select @ next = 0

Select @ location = charindex (',', @ str,@start)

While (@ location 0 and @ index > @ next)

Begin

Select @ start = @ location + 1

Select @ location = charindex (',', @ str,@start)

Select @ next = @ next + 1

End

If @ location = 0 select @ location = len (@ str) + 1-- if there is no comma exit, the comma is considered to be after the string

Select @ str_return = substring (@ str,@start,@location-@ start)-@ start must be the position after the comma or the initial value

If (@ index @ next) select @ str_return =''--if the two are not equal, it is because there are too few commas, or @ index is less than the initial value of @ next.

Return @ str_return

End

-- Test example

SELECT [dbo]. [getstrofindex] ('1Jing 2 Jing 3 Ji 4 a recorder breco creco dharma 4)

-- run result

/ *

, /

-- Note: a function with similar function happyflystone (Sniper without Gun) has also been written. The parameter has been extended to define the delimiter. I have posted it in [Leaf function sharing 16].

48. Get all Sunday dates according to the year

Go

-- create function

Create function GetWeekDays (@ year int)

Returns @ t table (Sunday varchar (20))

As

Begin

Insert @ t

Select substring (convert (varchar,dateadd (day,x,col), 120), 1mai 10) from

(select cast (cast (@ year as varchar (4)) +'- 1-1' as datetime) as col) a cross join

(select top 365 b8.i+b7.i + b6.i + b5.i + b4.i + b3.I + b2.i + b1.i + b0.i x

From (select 0 i union all select 1) b0

Cross join (select 0 i union all select 2) b1

Cross join (select 0 i union all select 4) b2

Cross join (select 0 i union all select 8) b3

Cross join (select 0 i union all select 16) b4

Cross join (select 0 i union all select 32) b5

Cross join (select 0 i union all select 64) b6

Cross join (select 0 i union all select 128) b7

Cross join (select 0 i union all select 256) b8

Order by 1) b where datepart (dw,dateadd (day,x,col)) = 1

Return

End

-- Test example

Select * from dbo.GetWeekDays (2011)

-- run result

/ *

Sunday

-

2011-01-02

2011-01-09

2011-01-16

2011-01-23

2011-01-30

2011-02-06

2011-02-13

2011-02-20

2011-02-27

2011-03-06

2011-03-13

2011-03-20

2011-03-27

2011-04-03

2011-04-10

2011-04-17

2011-04-24

2011-05-01

2011-05-08

2011-05-15

2011-05-22

2011-05-29

2011-06-05

2011-06-12

2011-06-19

2011-06-26

2011-07-03

2011-07-10

2011-07-17

2011-07-24

2011-07-31

2011-08-07

2011-08-14

2011-08-21

2011-08-28

2011-09-04

2011-09-11

2011-09-18

2011-09-25

2011-10-02

2011-10-09

2011-10-16

2011-10-23

2011-10-30

2011-11-06

2011-11-13

2011-11-20

2011-11-27

2011-12-04

2011-12-11

2011-12-18

2011-12-25

(52 row (s) affected)

, /

49. All dates between two times are generated

-- rewrite part of the code of liangCK as a function

-- create function

Create function generateTime

(

@ begin_date datetime

@ end_date datetime

)

Returns @ t table (date datetime)

As

Begin

With maco as

(

Select @ begin_date AS date

Union all

Select date+1 from maco

Where date+1 0

Begin

Set @ Sub = left (@ Str1, charindex (@ Split, @ Str1, @ Len + 1) + @ Len-1)

If charindex (@ Sub, @ Str2) = 0 return (0)

While charindex (@ Sub, @ Str1) > 0 set @ Str1 = replace (@ Str1, @ Sub,',')

While charindex (@ Sub, @ Str2) > 0 set @ Str2 = replace (@ Str2, @ Sub,',')

If len (@ Str1) len (@ Str2) return (0)

End

Return (1)

END

GO

-- Test example

Select dbo.fn_CompareString ('a _

Select dbo.fn_CompareString ('a _

-- run result

/ *

, /

51. Implement RSA encryption and decryption function in SQL SERVER (first edition)

/ *

Author: herowang (the wall that allows you to see the shadow)

Date: 2010.1.1

Note: please keep this information when reproduced.

For more information, please visit my blog: blog.csdn.net/herowang

* * /

First, the principle of RSA algorithm

The RSA algorithm is very simple and is summarized as follows:

Find two primes p and Q

Fetch n=p*q

Take t = (pmur1) * (qmur1)

Take any number e, which is required to satisfy e

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.