Des UDF (fonctions utilisateur) pour SQL Server

Quelques fonctions bien utiles pour développer avec SQL Server

Des fonctions en vrac écrites en transact SQL pour les développeurs.

Des UDF pour MS SQL Server
Code Transact SQL des fonctions
  1. /****************************************************************************/
  2. -- mise en majuscule de la première lettre et minuscule de toutes les autres
  3. /****************************************************************************/
  4. CREATE FUNCTION F_FIRST_CAP (@NAME VARCHAR(8000))
  5. RETURNS VARCHAR(8000)
  6. AS
  7. BEGIN
  8.    IF @NAME IS NULL
  9.       RETURN NULL
  10.    IF LEN(@NAME) = 1
  11.       RETURN UPPER(@NAME)
  12.    RETURN UPPER(SUBSTRING(@NAME, 1, 1)) +
  13.           LOWER(SUBSTRING(@NAME, 2, LEN(@NAME) -1))
  14. END
  15. GO
  16.  
  17. /****************************************************************************/
  18. -- table des jours de semaine
  19. /****************************************************************************/
  20. CREATE FUNCTION F_JOUR_SEMAINE ()
  21. RETURNS TABLE
  22. AS
  23. RETURN (SELECT 1 AS N, 'Lundi' AS JOUR
  24.         UNION
  25.         SELECT 2 AS N, 'Mardi' AS JOUR
  26.         UNION
  27.         SELECT 3 AS N, 'Mercredi' AS JOUR
  28.         UNION
  29.         SELECT 4 AS N, 'Jeudi' AS JOUR
  30.         UNION
  31.         SELECT 5 AS N, 'Vendredi' AS JOUR
  32.         UNION
  33.         SELECT 6 AS N, 'Samedi' AS JOUR
  34.         UNION
  35.         SELECT 7 AS N, 'Dimanche' AS JOUR)
  36. GO
  37.  
  38. /****************************************************************************/
  39. -- calcul de la date de Pâque
  40. /****************************************************************************/
  41. CREATE FUNCTION F_PAQUE (@AN INT)
  42.        RETURNS DATETIME
  43. AS
  44.  
  45. BEGIN
  46.  
  47. IF @AN IS NULL
  48.    RETURN NULL
  49.  
  50. DECLARE @G INT
  51. DECLARE @I INT
  52. DECLARE @J INT
  53. DECLARE @C INT
  54. DECLARE @H INT
  55. DECLARE @L INT
  56. DECLARE @JourPaque INT
  57. DECLARE @MoisPaque INT
  58. DECLARE @DimPaque DATETIME
  59.  
  60. SET @G = @AN % 19
  61. SET @C = @AN / 100
  62. SET @H = (@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30
  63. SET @I = @H - (@H / 28) * (1 - (@H / 28) * (29 / (@H + 1)) * ((21 - @G) / 11))
  64. SET @J = (@AN + @AN / 4 + @I + 2 - @C + @C / 4) % 7
  65.  
  66. SET @L = @I - @J
  67. SET @MoisPaque = 3 + (@L + 40) / 44
  68. SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)
  69.  
  70.  
  71. SET @DimPaque = CAST(CAST(@AN AS VARCHAR(4)) +
  72.                 CASE
  73.                    WHEN @MoisPaque < 10 THEN '0' + CAST(@MoisPaque AS CHAR(1))
  74.                                         ELSE       CAST(@MoisPaque AS CHAR(2))
  75.                 END +
  76.                 CASE
  77.                    WHEN @JourPaque < 10 THEN '0' + CAST(@JourPaque AS CHAR(1))
  78.                                         ELSE       CAST(@JourPaque AS CHAR(2))
  79.                 END
  80.                 AS DATETIME)
  81.  
  82. RETURN @DimPaque
  83.  
  84. END
  85. GO
  86.  
  87. /****************************************************************************/
  88. -- Transformation d'un entier en chaine binaire VARCHAR
  89. /****************************************************************************/
  90. CREATE FUNCTION F_INT_TO_BIT (@I INT)
  91.        RETURNS VARCHAR(512)
  92. AS
  93. BEGIN
  94. -- effet de bord
  95.    IF @I IS NULL
  96.       RETURN NULL
  97. -- valeur basique
  98.    IF @I = 0
  99.       RETURN '0'
  100. -- signe négatif
  101.    DECLARE @SIGNE VARCHAR(1)
  102.    SET @SIGNE = ''
  103.    IF @I < 0
  104.       SET @SIGNE = '-'
  105. -- conversion
  106.    DECLARE @BIT_OUT VARCHAR(512)
  107.    SET @BIT_OUT = ''
  108.    WHILE NOT @I = 0
  109.    BEGIN
  110.       IF @I % 2 = 0
  111.         SET @BIT_OUT = '0' +  @BIT_OUT
  112.       ELSE
  113.         SET @BIT_OUT = '1' +  @BIT_OUT
  114.       SET @I = @I / 2
  115.    END
  116.    RETURN @SIGNE + @BIT_OUT
  117. END
  118. GO
  119.  
  120. /****************************************************************************/
  121. -- retourne le nom de l'utilisateur d'un id (userid) donné
  122. /****************************************************************************/
  123. CREATE FUNCTION F_FORMATUSER(@USERID SMALLINT) RETURNS VARCHAR(32) AS
  124. BEGIN
  125.    RETURN(CAST(SUBSTRING(USER_NAME(@USERID), 1, 32) AS VARCHAR(32)))
  126. END
  127. GO
  128.  
  129. /****************************************************************************/
  130. -- retourne une chaine interprétable en chaine SQL
  131. /****************************************************************************/
  132. CREATE FUNCTION F_QUOTESTR(@S VARCHAR(8000))
  133. RETURNS VARCHAR(8000)
  134. AS
  135. BEGIN
  136.    DECLARE @OUT VARCHAR(8000)
  137.    SET @OUT = CASE
  138.                  WHEN @S IS NULL
  139.                       THEN 'NULL'
  140.                  ELSE ''''+REPLACE(@S, '''', '''''')+''''
  141.               END
  142.    RETURN (@OUT)
  143. END
  144. GO
  145.  
  146. /****************************************************************************/
  147. -- retourne une chaine de caractère représentant une date au format ISO
  148. /****************************************************************************/
  149. CREATE FUNCTION F_DATEISO(@D DATETIME)
  150. RETURNS VARCHAR(8000)
  151. AS
  152. BEGIN
  153.    DECLARE @OUT VARCHAR(12)
  154.    SET @OUT = CASE
  155.                  WHEN @D IS NULL
  156.                       THEN 'NULL'
  157.                  ELSE ''''+CONVERT(CHAR(10), @D, 121)+''''
  158.               END
  159.    RETURN (@OUT)
  160. END
  161. GO
  162.  
  163. /****************************************************************************/
  164. -- obtient la liste des colonnes d'une table
  165. /****************************************************************************/
  166. CREATE FUNCTION F_LISTCOLS (@NOM_TABLE VARCHAR(128))
  167. RETURNS VARCHAR(8000) AS
  168. BEGIN
  169.    DECLARE @RETVAL VARCHAR(8000)
  170.    SET @RETVAL = ''
  171.    SELECT @RETVAL = @RETVAL +COLUMN_NAME+', '
  172.    FROM   INFORMATION_SCHEMA.COLUMNS
  173.    WHERE  TABLE_NAME = @NOM_TABLE
  174.    IF @RETVAL IS NULL
  175.       RETURN NULL
  176.    IF  @RETVAL = ''
  177.       RETURN NULL
  178.    SET @RETVAL = SUBSTRING(@RETVAL, 1, LEN(@RETVAL) -1)
  179.    RETURN @RETVAL
  180. END
  181. GO
  182.  
  183. /****************************************************************************/
  184. -- purge de caractères indésirables
  185. /****************************************************************************/
  186. -- exemple : F_RESTRICT('à Paris...?', 'abcdefghijklmnopqrstuvwxyz') => 'aris'
  187. CREATE FUNCTION F_RESTRICT (@IN VARCHAR (8000),
  188.                              @CHARSOK VARCHAR(256))
  189. RETURNS VARCHAR (8000)
  190. AS
  191. BEGIN
  192. -- effets de bord
  193.    IF @IN IS NULL
  194.       RETURN NULL
  195.    IF @CHARSOK IS NULL
  196.       RETURN NULL
  197.    IF LEN(@IN) = 0
  198.       RETURN @IN
  199. -- initialisation
  200.    DECLARE @I INTEGER
  201.    DECLARE @OUT VARCHAR(8000)
  202.    SET @OUT = ''
  203. -- lecture caractère par caractère
  204.    SET @I =1
  205.    WHILE @I <= LEN(@IN)
  206.    BEGIN
  207.       IF PATINDEX('%' + SUBSTRING(@IN, @I, 1)+ '%', @CHARSOK) > 0
  208.          SET @OUT = @OUT + SUBSTRING(@IN, @I, 1)
  209.       SET @I = @I + 1
  210.    END
  211.    RETURN @OUT
  212. END
  213. GO
  214.  
  215. /****************************************************************************/
  216. -- conversion d'heure minute seconde littérale en heure décimale
  217. /****************************************************************************/
  218. CREATE FUNCTION F_CONVERT_HMS_HD (@HMS CHAR(8))
  219. RETURNS FLOAT
  220. AS
  221.  
  222. BEGIN
  223.  
  224. DECLARE @H FLOAT
  225. DECLARE @M FLOAT
  226. DECLARE @S FLOAT
  227. DECLARE @RETVAL FLOAT
  228.  
  229. -- cas trivial
  230. IF @HMS IS NULL
  231.    RETURN NULL
  232.  
  233. -- voir si saisie erronée (pas de chiffres)
  234. SET @HMS = REPLACE(@HMS, ':', '')
  235. IF LEN(@HMS) <> 6
  236.    RETURN NULL
  237.  
  238. DECLARE @I INTEGER
  239. SET @I = 1
  240. WHILE @I < 7
  241. BEGIN
  242.    IF SUBSTRING(@HMS, @I, 1) NOT BETWEEN '0' AND '9'
  243.       RETURN NULL
  244.    SET @I = @I + 1
  245. END
  246.  
  247. -- la saisie est correcte
  248. SET @H = CAST(SUBSTRING(@HMS, 1, 2) AS FLOAT)
  249. SET @M = CAST(SUBSTRING(@HMS, 3, 2) AS FLOAT) / 60.0
  250. SET @S = CAST(SUBSTRING(@HMS, 5, 2) AS FLOAT) / 3600.0
  251. SET @RETVAL = @H + @M + @S
  252.  
  253. RETURN @RETVAL
  254.  
  255. END
  256. GO
  257.  
  258. /****************************************************************************/
  259. -- Compte le nombre d'occurences d'une sous chaine dans une chaine
  260. /****************************************************************************/
  261. CREATE FUNCTION dbo.F_COUNTSTR (@STR VARCHAR(8000), @PATTERN VARCHAR(8000))
  262. RETURNS INTEGER
  263. AS
  264. BEGIN
  265.  
  266. DECLARE @I INTEGER
  267.  
  268. -- cas trivial données en entrée NULL
  269. IF @STR IS NULL OR @PATTERN IS NULL
  270. BEGIN
  271.    SET @I = NULL
  272.    RETURN @I
  273. END
  274.  
  275. -- cas trivial données en entrée vide
  276. IF @STR ='' OR @PATTERN =''
  277. BEGIN
  278.    SET @I = 0
  279.    RETURN @I
  280. END
  281.  
  282. -- cas général
  283.  
  284. DECLARE @STR2 VARCHAR(8000)
  285.  
  286. SET @STR2 = @STR
  287. SET @I = 0
  288.  
  289. WHILE PATINDEX('%'+@PATTERN+'%', @STR2) > 0
  290. BEGIN
  291.    SET @I = @I +1
  292.    IF LEN(@STR2) > PATINDEX('%'+@PATTERN+'%', @STR2) + LEN(@PATTERN)
  293.       SET @STR2 = SUBSTRING(@STR2, PATINDEX('%'+@PATTERN+'%', @STR2)
  294.                         + LEN(@PATTERN), LEN(@STR2) - PATINDEX('%'+@PATTERN+'%', @STR2)
  295.                         -  LEN(@PATTERN)+1)
  296.    ELSE
  297.       SET @STR2 = ''
  298. END
  299.  
  300. RETURN @I
  301.  
  302. END
  303. GO
  304.  
  305. /****************************************************************************/
  306. -- remplace un datetime par une datetime avec heure à zero
  307. /****************************************************************************/
  308. CREATE FUNCTION F_DATETIME_AS_DATE (@DT DATETIME)
  309. RETURNS DATETIME AS
  310. BEGIN
  311.    RETURN CAST(FLOOR(CAST(@DT AS FLOAT)) AS DATETIME)
  312. END
  313. GO
  314.  
  315. /****************************************************************************/
  316. -- soundex 2 (Celko SQL Avancé p 86)
  317. /****************************************************************************/
  318. CREATE FUNCTION F_SOUNDEX2 (@NAME VARCHAR (128))
  319. RETURNS CHAR (4)
  320. AS
  321. BEGIN
  322.    DECLARE @SNDX2 CHAR(4)
  323.    SET @SNDX2 = '    '
  324.    IF @NAME IS NULL
  325.       RETURN @SNDX2
  326.    DECLARE @FIRSTLET CHAR(1)
  327.    DECLARE @XGRAM VARCHAR(4)
  328. -- mise en majuscule
  329.    SET @NAME = UPPER(@NAME)
  330. -- dé diacritisation
  331.    SET @NAME = dbo.F_TRANSLATE(@NAME, 'ÀÂÄÉÈÊËÎÏÔÖÙÛÜÇ', 'AAAEEEEIIOOUUUC')
  332. -- restriction aux letttres
  333.    SET @NAME = dbo.F_RESTRICT(@NAME, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
  334.    IF @NAME = ''
  335.       RETURN @SNDX2
  336. -- substitution des préfixes
  337.    IF LEN(@NAME) >= 3
  338.    BEGIN
  339.       SET @XGRAM = SUBSTRING(@NAME, 1, 3)
  340.       IF @XGRAM = 'MAC'
  341.          IF LEN(@NAME) = 3
  342.             SET @NAME = 'MCC'
  343.          ELSE
  344.             SET @NAME = 'MCC'+ SUBSTRING(@NAME, 4, LEN(@NAME) -3)              
  345.       IF @XGRAM = 'SCH'
  346.          IF LEN(@NAME) = 3
  347.             SET @NAME = 'SSS'
  348.          ELSE
  349.             SET @NAME = 'SSS'+ SUBSTRING(@NAME, 4, LEN(@NAME) -3)    
  350.    END
  351.    IF LEN(@NAME) >= 2
  352.    BEGIN
  353.       SET @XGRAM = SUBSTRING(@NAME, 1, 2)
  354.       IF @XGRAM = 'KN'
  355.          IF LEN(@NAME) = 2
  356.             SET @NAME = 'NN'
  357.          ELSE
  358.             SET @NAME = 'NN'+ SUBSTRING(@NAME, 3, LEN(@NAME) -2)
  359.       IF @XGRAM = 'PF'
  360.          IF LEN(@NAME) = 2
  361.             SET @NAME = 'FF'
  362.          ELSE
  363.             SET @NAME = 'FF'+ SUBSTRING(@NAME, 3, LEN(@NAME) -2)
  364.       IF @XGRAM = 'PH'
  365.          IF LEN(@NAME) = 2
  366.             SET @NAME = 'FF'
  367.          ELSE
  368.             SET @NAME = 'FF'+ SUBSTRING(@NAME, 3, LEN(@NAME) -2)  
  369.    END
  370.    IF LEN(@NAME) >= 1
  371.    BEGIN
  372.       SET @XGRAM = SUBSTRING(@NAME, 1, 1)
  373.       IF @XGRAM = 'K'
  374.          IF LEN(@NAME) = 1
  375.             SET @NAME = 'C'
  376.          ELSE
  377.             SET @NAME = 'C'+ SUBSTRING(@NAME, 2, LEN(@NAME) -1)
  378.    END
  379.  
  380. -- transformations sauf première lettre                                                   ''
  381.    SET @FIRSTLET = SUBSTRING(@NAME, 1, 1)
  382.    IF LEN(@NAME) > 1
  383.       SET @NAME = dbo.F_TRANSLATE(SUBSTRING(@NAME, 2, LEN(@NAME)-1), 'AEIOUY', 'AAAAAA')
  384.    ELSE
  385.       SET @NAME = ''
  386.    SET @NAME = REPLACE(@NAME, 'DG',   'GG')
  387.    SET @NAME = REPLACE(@NAME, 'CAAN', 'TAAN')
  388.    SET @NAME = REPLACE(@NAME, 'D',    'T')
  389.    SET @NAME = REPLACE(@NAME, 'NST',  'NSS')
  390.    SET @NAME = REPLACE(@NAME, 'AV',   'AF')
  391.    SET @NAME = REPLACE(@NAME, 'Q',    'G')
  392.    SET @NAME = REPLACE(@NAME, 'Z',    'S')
  393.    SET @NAME = REPLACE(@NAME, 'M',    'N')
  394.    SET @NAME = REPLACE(@NAME, 'KN',   'NN')
  395.    SET @NAME = REPLACE(@NAME, 'K',    'C')
  396. -- remplacement des H par A sauf suivi et précédé par A (exemple '...AHA...')
  397.    SET @NAME = REPLACE(@NAME, 'AHA',  'AhA')
  398.    SET @NAME = REPLACE(@NAME, 'H',    'A')          
  399.    SET @NAME = REPLACE(@NAME, 'AhA',  'AHA')
  400. -- remplacements divers
  401.    SET @NAME = REPLACE(@NAME, 'AW',   'A')
  402.    SET @NAME = REPLACE(@NAME, 'PH',   'FF')
  403.    SET @NAME = REPLACE(@NAME, 'SCH',  'SSS')
  404. -- suppression A en fin de mot
  405.    WHILE SUBSTRING(@NAME, LEN(@NAME), 1) = 'A'
  406.       IF LEN(@NAME) > 1
  407.          SET @NAME = SUBSTRING(@NAME, 1, LEN(@NAME)-1)
  408.       ELSE
  409.          SET @NAME = ''
  410. -- suppression S en fin
  411.    WHILE SUBSTRING(@NAME, LEN(@NAME), 1) = 'S'
  412.       IF LEN(@NAME) > 1
  413.          SET @NAME = SUBSTRING(@NAME, 1, LEN(@NAME)-1)
  414.       ELSE
  415.          SET @NAME = ''
  416. -- supression NT en fin
  417.    IF LEN(@NAME) >= 2
  418.      IF SUBSTRING(@NAME, LEN(@NAME)-1, 2) = 'NT'
  419.         IF LEN(@NAME) > 2
  420.            SET @NAME = SUBSTRING(@NAME, 1, LEN(@NAME) -2)
  421. -- suppression des A
  422.    SET @NAME = REPLACE(@NAME, 'A', '')
  423. -- test effet de bord : @NAME vide
  424.    IF @NAME = ''
  425.    BEGIN
  426.       SET @SNDX2 = @FIRSTLET +'   '
  427.       RETURN @SNDX2
  428.    END
  429. -- suppression des répétitions
  430.    DECLARE @OUT VARCHAR(4)
  431.    SET @OUT = @FIRSTLET
  432.    DECLARE @I INTEGER
  433.    DECLARE @C CHAR(1)
  434.    DECLARE @CC CHAR(1)
  435.    SET @I = 1
  436.    SET @CC = ''
  437.    WHILE @I <= LEN(@NAME)
  438.    BEGIN
  439.       SET @C = SUBSTRING(@NAME, @I, 1)
  440.       IF @C <> @CC
  441.       BEGIN
  442.          IF LEN(@OUT) < 4
  443.             SET @OUT = @OUT + @C          
  444.          SET @CC = @C
  445.       END
  446.       IF LEN(@OUT) = 4
  447.          BREAK
  448.       SET @I = @I + 1
  449.    END
  450.    SET @SNDX2 = @OUT
  451.    RETURN @SNDX2
  452. END
  453. GO
  454.  
  455. /****************************************************************************/
  456. -- remplacement par substitution de caractères
  457. /****************************************************************************/
  458. -- exemple : F_TRANSLATE('à Paris...', 'à.', 'a') => 'a paris'
  459. CREATE FUNCTION F_TRANSLATE (@VALIN VARCHAR (8000),
  460.                               @FROM VARCHAR(256), @TO VARCHAR(256))
  461. RETURNS VARCHAR (8000)
  462. AS
  463. BEGIN
  464. -- effets de bord
  465.    IF @VALIN IS NULL
  466.       RETURN NULL
  467.    IF @FROM IS NULL OR @TO IS NULL
  468.       RETURN NULL
  469.    IF LEN(@VALIN) = 0
  470.       RETURN @VALIN
  471. -- initialisation
  472.    DECLARE @I INTEGER
  473.    DECLARE @OUT VARCHAR(8000)
  474.    SET @OUT = ''
  475. -- lecture caractère par caractère
  476.    SET @I =1
  477.    WHILE @I <= LEN(@VALIN)
  478.    BEGIN
  479.       IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0
  480.       BEGIN
  481.          IF LEN(@TO) >= PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM)
  482.             SET @OUT = @OUT + SUBSTRING(@TO, PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1)
  483.       END
  484.       ELSE
  485.          SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1)
  486.       SET @I = @I + 1
  487.    END
  488.    RETURN @OUT
  489. END
  490. GO
  491.  
  492. /****************************************************************************/
  493. -- conversion d'heure décimale en heure minute seconde littérale
  494. /****************************************************************************/
  495. CREATE FUNCTION F_CONVERT_HD_HMS (@HD FLOAT)
  496. RETURNS VARCHAR(8)
  497. AS
  498.  
  499. BEGIN
  500.  
  501. DECLARE @H INTEGER
  502. DECLARE @M INTEGER
  503. DECLARE @S INTEGER
  504. DECLARE @RETVAL VARCHAR(8)
  505.  
  506. -- cas trivial
  507. IF @HD IS NULL
  508.    RETURN NULL
  509.  
  510. -- récupération des heures, minutes, secondes
  511. SET @H  = FLOOR(@HD)
  512. SET @HD = @HD - @H
  513. SET @HD = @HD * 60
  514. SET @M  = FLOOR(@HD)
  515. SET @HD = @HD - @M
  516. SET @HD = @HD * 60
  517. SET @S  = FLOOR(@HD)
  518.  
  519. IF @H < 10
  520.    SET @RETVAL = '0'+CAST(@H AS CHAR(1))+':'
  521. ELSE
  522.    SET @RETVAL = CAST(@H AS CHAR(2))+':'
  523. IF @M < 10
  524.    SET @RETVAL = @RETVAL + '0' + CAST(@M AS CHAR(1))+':'
  525. ELSE
  526.    SET @RETVAL = @RETVAL + CAST(@M AS CHAR(2))+':'
  527. IF @S < 10
  528.    SET @RETVAL = @RETVAL + '0' + CAST(@S AS CHAR(1))
  529. ELSE
  530.    SET @RETVAL = @RETVAL + CAST(@S AS CHAR(2))+':'
  531.  
  532. RETURN @RETVAL
  533.  
  534. END
  535. GO
  536.  
  537. /****************************************************************************/
  538. -- affichage heure et minute extrait d'une date
  539. /****************************************************************************/
  540. CREATE FUNCTION F_DATETIME_AS_HM (@DT DATETIME)
  541. RETURNS CHAR(5) AS
  542. BEGIN
  543.    IF @DT IS NULL RETURN NULL
  544.    DECLARE @H INT
  545.    DECLARE @M INT
  546.    SET @H = DATEPART(HOUR, @DT)
  547.    SET @M = DATEPART(MINUTE, @DT)
  548.    DECLARE @RETVAL VARCHAR(5)
  549.    IF @H < 10
  550.       SET @RETVAL = '0' + CAST(@H AS CHAR(1))+':'
  551.    ELSE
  552.       SET @RETVAL = CAST(@H AS CHAR(2))+':'
  553.    IF @M < 10
  554.       SET @RETVAL = @RETVAL + '0' + CAST(@M AS CHAR(1))
  555.    ELSE
  556.       SET @RETVAL = @RETVAL + CAST(@M AS CHAR(2))
  557.    RETURN CAST(@RETVAL AS CHAR(5))
  558. END
  559. GO
  560.  
  561. /****************************************************************************/
  562. -- remplace un datetime par une datetime avec date à zero
  563. /****************************************************************************/
  564. CREATE FUNCTION F_DATETIME_AS_HOUR (@DT DATETIME)
  565. RETURNS DATETIME AS
  566. BEGIN
  567.    RETURN CAST(CAST(@DT AS FLOAT) - FLOOR(CAST(@DT AS FLOAT)) AS DATETIME)
  568. END
  569. GO
  570.  
  571. /****************************************************************************/
  572. -- compléte avec des zéros un nombre converti en chaine de caractères
  573. /****************************************************************************/
  574. CREATE FUNCTION dbo.F_PAD_ZERO (@INT INTEGER, @NBR_ZERO INTEGER)
  575. RETURNS VARCHAR(8000)
  576. AS
  577. BEGIN
  578.  
  579. DECLARE @OUT VARCHAR(8000)
  580.  
  581. -- cas trivial @NBR_ZERO est NULL
  582. IF @NBR_ZERO IS NULL
  583.    RETURN @OUT
  584.  
  585. -- cas trivial @NBR_ZERO < longueur de @INT
  586. IF @INT IS NOT NULL
  587.    IF @NBR_ZERO < LEN(CAST(@INT AS VARCHAR(8000)))
  588.       RETURN @OUT
  589.  
  590. -- cas général
  591. SET @OUT = CAST(@INT AS VARCHAR(8000))
  592. WHILE LEN(@OUT) < @NBR_ZERO
  593. BEGIN
  594.    SET @OUT = '0'+@OUT
  595. END
  596.  
  597.    RETURN @OUT
  598.  
  599. END
  600. GO
  601.  
  602. /****************************************************************************/
  603. /* conversion code hexa en décimal                                          */
  604. /****************************************************************************/
  605. CREATE FUNCTION F_HEX_TO_DEC (@HEX VARCHAR(16))
  606.        RETURNS BIGINT
  607.  
  608. AS
  609. BEGIN
  610. -- effet de bord
  611.    IF @HEX IS NULL
  612.       RETURN NULL
  613. -- valeur basique
  614.    SET @HEX = RTRIM(LTRIM(UPPER(@HEX)))
  615.    IF @HEX = ''
  616.       RETURN NULL
  617. -- signe négatif
  618.    DECLARE @SIGNE VARCHAR(1)
  619.    SET @SIGNE = ''
  620.    IF SUBSTRING(@HEX, 1, 1) = '-'
  621.    BEGIN
  622.       SET @SIGNE = '-'
  623.       SET @HEX = SUBSTRING(@HEX, 2, LEN(@HEX) - 1)
  624.    END
  625.    IF SUBSTRING(@HEX, 1, 1) = '+'
  626.    BEGIN
  627.       SET @SIGNE = ''
  628.       SET @HEX = SUBSTRING(@HEX, 2, LEN(@HEX) - 1)
  629.    END
  630.    SET @HEX = RTRIM(@HEX)
  631. -- conversion
  632.    DECLARE @INT_OUT BIGINT
  633.    DECLARE @CHR CHAR(1)
  634.    DECLARE @I INT
  635.    SET @INT_OUT = 0
  636.    SET @I = 0
  637.    WHILE @I < LEN(@HEX)
  638.    BEGIN
  639.       SET @CHR = SUBSTRING(@HEX, LEN(@HEX) - @I, 1)
  640.       SET @INT_OUT = @INT_OUT + POWER(16, @I) * CASE @CHR
  641.                                                    WHEN '0' THEN 0
  642.                                                    WHEN '1' THEN 1
  643.                                                    WHEN '2' THEN 2
  644.                                                    WHEN '3' THEN 3
  645.                                                    WHEN '4' THEN 4
  646.                                                    WHEN '5' THEN 5
  647.                                                    WHEN '6' THEN 6
  648.                                                    WHEN '7' THEN 7
  649.                                                    WHEN '8' THEN 8
  650.                                                    WHEN '9' THEN 9
  651.                                                    WHEN 'A' THEN 10
  652.                                                    WHEN 'B' THEN 11
  653.                                                    WHEN 'C' THEN 12
  654.                                                    WHEN 'D' THEN 13
  655.                                                    WHEN 'E' THEN 14
  656.                                                    WHEN 'F' THEN 15
  657.                                                    ELSE NULL
  658.                                                 END
  659.       SET @I = @I + 1
  660.    END
  661.    RETURN @INT_OUT * CASE @SIGNE WHEN '-' THEN -1 ELSE 1 END
  662. END
  663. GO
  664. -- liste toutes les colonnes d'une table
  665. CREATE FUNCTION F_LISTE_COLS (@TABLE_NAME VARCHAR(128))
  666.    RETURNS VARCHAR(8000)
  667. AS
  668.  
  669. BEGIN
  670.  
  671. -- liste des noms de colonnes dans
  672.    DECLARE @ColumnList VARCHAR(8000)
  673.    SET @ColumnList = ''
  674.  
  675. -- obtention de la liste des colonnes pour la requête de recherche
  676.    SELECT @ColumnList = @ColumnList + COLUMN_NAME +', '
  677.    FROM   INFORMATION_SCHEMA.COLUMNS
  678.    WHERE  TABLE_NAME = @TABLE_NAME
  679.  
  680.    RETURN SUBSTRING(@ColumnList, 1, LEN(@ColumnList) -1)
  681.  
  682. END
  683. GO
  684.  
  685. -- exemple d'utilisation :
  686. SELECT dbo.F_LISTE_COLS('T_CONTACT_CTC')
  687.  
  688. /************************************************************
  689.   CALCUL DES SAISONS - basé sur l'algorithmes de Meeus
  690.  ("Astronomical Algorithms", 1991) modifié par Simon Cassidy
  691. -------------------------------------------------------------
  692.  Frédéric BROUARD - SQLpro - 2004-06-27 - Transact SQL
  693. ************************************************************/
  694.  
  695. CREATE FUNCTION F_SUB_SEASONS (@JDME FLOAT(50))
  696.    RETURNS DATETIME
  697. AS
  698. -- sous fonction utilisée par les 4 calculs de saisons
  699. BEGIN
  700.  
  701. IF @JDME IS NULL
  702.    RETURN NULL
  703.  
  704. DECLARE @T FLOAT(50)
  705. DECLARE @S FLOAT(50)
  706. DECLARE @W FLOAT(50)
  707. DECLARE @L FLOAT(50)
  708. DECLARE @JD FLOAT(50)
  709. DECLARE @D DATETIME
  710.  
  711. SET @T = (@JDME - 2451545.0) / 36525              
  712.  
  713. SET @S =
  714.   485 * COS(0.43563418129778464 +   33.757041381353048   * @T) +
  715.   203 * COS(0.64978608051748876 +  575.33848531501758    * @T) +
  716.   199 * COS(0.73443454923921381 +    0.35231216280757538 * @T) +
  717.   182 * COS(0.48607419668042079 + 7771.3771552463541     * @T) +
  718.   156 * COS(1.2765338149086527  +  786.04194554533876    * @T) +
  719.   136 * COS(1.2482594810263443  +  393.02097277266938    * @T) +
  720.    77 * COS(0.39339721339952183 + 1150.6769706300352     * @T) +
  721.    74 * COS(1.6880824525289155  +   52.969102188531025   * @T) +
  722.    70 * COS(0.76061448801912879 +  157.7343580417903     * @T) +
  723.    58 * COS(0.34574972482007665 +  588.4926828214484     * @T) +
  724.    52 * COS(1.69593643416289    +    2.6298272103200158  * @T) +
  725.    50 * COS(0.366868208769208   +   39.81490468210017    * @T) +
  726.    45 * COS(0.82972952639810416 +  522.36940057977904    * @T) +
  727.    44 * COS(0.43895030687657388 +  550.75533081445974    * @T) +
  728.    29 * COS(1.063429113240145   +   77.552256689088878   * @T) +
  729.    18 * COS(0.96202548369927443 + 1179.0629008647159     * @T) +
  730.    17 * COS(1.5496778428457652  +   79.629809364200341   * @T) +
  731.    16 * COS(1.7111207986552408  + 1097.7078858947966     * @T) +
  732.    14 * COS(1.7411404617895434  +  548.67777813934822    * @T) +
  733.    12 * COS(1.6648695734773908  +  254.43144545527034    * @T) +
  734.    12 * COS(1.5203563114122605  +  557.31427814345443    * @T) +
  735.    12 * COS(0.36320301734001997 +  606.97767436883066    * @T) +
  736.     9 * COS(0.48397980157802756 +   21.32991313471798    * @T) +
  737.     8 * COS(0.2696533694331239  +  294.24635013737048    * @T)
  738.  
  739. SET @W = ( 35999.373*@T - 2.47 ) * PI() / 180
  740.  
  741. SET @L = 1 + 0.0334*COS(@W) + 0.0007*COS(2*@W)
  742.  
  743. SET @JD = @JDME + (0.00001*@S/@L)
  744.  
  745. SET @JD = @JD - 2415020.50208142228
  746.  
  747. SET @D = CAST(FLOOR(@JD) AS DATETIME)
  748.  
  749. RETURN @D
  750.  
  751. END
  752.  
  753. GO
  754.  
  755. CREATE FUNCTION F_WINTER_DATE (@Y INT)
  756.    RETURNS DATETIME
  757. AS
  758.  
  759. -- calcul de la date de début d'hiver
  760.  
  761. BEGIN
  762.  
  763. -- @Y est l'année considérée
  764.  
  765. -- effets de bord : année absente
  766. IF @Y IS NULL
  767.    RETURN NULL
  768.  
  769. -- limites de calculs de l'an 1000 à l'an 3000
  770. IF NOT @Y BETWEEN 1000 AND 3000
  771.    RETURN NULL
  772.  
  773. DECLARE @M FLOAT(50)
  774. DECLARE @JDME FLOAT(50)
  775.  
  776. SET @M = (CAST(@Y AS FLOAT(50)) - 2000.0) / 1000.0
  777. SET @JDME = 2451900.05952
  778.            + 365242.74049 * @M
  779.            -      0.06223 * SQUARE(@M)
  780.            -      0.00823 * POWER (@M, 3)
  781.            +      0.00032 * POWER (@M, 4)
  782.  
  783. RETURN dbo.F_SUB_SEASONS (@JDME)
  784.  
  785. END
  786. GO
  787.  
  788. CREATE FUNCTION F_AUTUMN_DATE (@Y INT)
  789.    RETURNS DATETIME
  790. AS
  791.  
  792. -- calcul de la date de début d'automne
  793.  
  794. BEGIN
  795.  
  796. -- @Y est l'année considérée
  797.  
  798. -- effets de bord : année absente
  799. IF @Y IS NULL
  800.    RETURN NULL
  801.  
  802. -- limites de calculs de l'an 1000 à l'an 3000
  803. IF NOT @Y BETWEEN 1000 AND 3000
  804.    RETURN NULL
  805.  
  806. DECLARE @M FLOAT(50)
  807. DECLARE @JDME FLOAT(50)
  808.  
  809. SET @M = (CAST(@Y AS FLOAT(50)) - 2000.0) / 1000.0
  810. SET @JDME = 2451810.21715
  811.            + 365242.01767 * @M
  812.            -      0.11575 * SQUARE(@M)
  813.            +      0.00337 * POWER (@M, 3)
  814.            +      0.00078 * POWER (@M, 4)
  815.  
  816. RETURN dbo.F_SUB_SEASONS (@JDME)
  817.  
  818. END
  819. GO
  820.  
  821. CREATE FUNCTION F_SUMMER_DATE (@Y INT)
  822.    RETURNS DATETIME
  823. AS
  824.  
  825. -- calcul de la date de début de l'été
  826.  
  827. BEGIN
  828.  
  829. -- @Y est l'année considérée
  830.  
  831. -- effets de bord : année absente
  832. IF @Y IS NULL
  833.    RETURN NULL
  834.  
  835. -- limites de calculs de l'an 1000 à l'an 3000
  836. IF NOT @Y BETWEEN 1000 AND 3000
  837.    RETURN NULL
  838.  
  839. DECLARE @M FLOAT(50)
  840. DECLARE @JDME FLOAT(50)
  841.  
  842. SET @M = (CAST(@Y AS FLOAT(50)) - 2000.0) / 1000.0
  843. SET @JDME = 2451716.56767
  844.            + 365241.62603 * @M
  845.            +      0.00325 * SQUARE(@M)
  846.            +      0.00888 * POWER (@M, 3)
  847.            -      0.00030 * POWER (@M, 4)
  848.  
  849. RETURN dbo.F_SUB_SEASONS (@JDME)
  850.  
  851. END
  852. GO
  853.  
  854. CREATE FUNCTION F_SPRING_DATE (@Y INT)
  855.    RETURNS DATETIME
  856. AS
  857.  
  858. -- calcul de la date de début du printemps
  859.  
  860. BEGIN
  861.  
  862. -- @Y est l'année considérée
  863.  
  864. -- effets de bord : année absente
  865. IF @Y IS NULL
  866.    RETURN NULL
  867.  
  868. -- limites de calculs de l'an 1000 à l'an 3000
  869. IF NOT @Y BETWEEN 1000 AND 3000
  870.    RETURN NULL
  871.  
  872. DECLARE @M FLOAT(50)
  873. DECLARE @JDME FLOAT(50)
  874. DECLARE @T FLOAT(50)
  875. DECLARE @S FLOAT(50)
  876. DECLARE @W FLOAT(50)
  877. DECLARE @L FLOAT(50)
  878. DECLARE @JD FLOAT(50)
  879. DECLARE @D DATETIME
  880.  
  881. SET @M = (CAST(@Y AS FLOAT(50)) - 2000.0) / 1000.0
  882. SET @JDME = 2451623.80984
  883.            + 365242.37404 * @M
  884.            +      0.05169 * SQUARE(@M)
  885.            -      0.00411 * POWER (@M, 3)
  886.            -      0.00057 * POWER (@M, 4)
  887.  
  888. RETURN dbo.F_SUB_SEASONS (@JDME)
  889.  
  890. END
  891. GO
  892.  
  893. /***********************
  894.  EXEMPLE D'UTILISATION
  895. ***********************/
  896.  
  897. SELECT 'PRINTEMPS' AS SAISON, dbo.F_SPRING_DATE(2000) AS DATE_DEBUT
  898. UNION
  899. SELECT 'ÉTÉ'       AS SAISON, dbo.F_SUMMER_DATE(2000) AS DATE_DEBUT
  900. UNION
  901. SELECT 'AUTOMNE'   AS SAISON, dbo.F_AUTUMN_DATE(2000) AS DATE_DEBUT
  902. UNION
  903. SELECT 'HIVER'     AS SAISON, dbo.F_WINTER_DATE(2000) AS DATE_DEBUT
  904. ORDER BY 2
  905.  
  906. /************************************************************
  907.   CONVERSION DE NOMBRE EN LITTERAUX :
  908.   sous fonctions :
  909.      F_NEC_20    : transformation des nombres de 1 à 19 en littéraux
  910.      F_NEC_100   : transformation des nombres de 20 à 99 en littéraux
  911.      F_NEC_0_100 : transformation des nombres de 0 à 100 en littéraux
  912.   fonction principale :
  913.      F_NOMBRE_EN_CHIFFRE : transformation de n'importe quel nombre
  914.      entier de l'intervalle [- 2 147 483 648 ; 2 147 483 647]
  915. -------------------------------------------------------------
  916.  Frédéric BROUARD - SQLpro - 2004-08-07 - Transact SQL
  917. ************************************************************/
  918.  
  919. CREATE FUNCTION F_NEC_20 (@I INT)
  920.    RETURNS VARCHAR(16)
  921. AS
  922.  
  923. /******************************************************************************
  924. * Frédéric BROUARD - SQLpro - 2004-08-07                                      *
  925. * Sous procédure de transformation des nombres de 1 à 19 en littéraux         *
  926. ******************************************************************************/
  927.  
  928. BEGIN
  929.  
  930. DECLARE @RETVAL VARCHAR(256)
  931.  
  932. SET @RETVAL =
  933. CASE
  934.    WHEN @I=1 THEN 'UN'  
  935.    WHEN @I=2 THEN 'DEUX'  
  936.    WHEN @I=3 THEN 'TROIS'
  937.    WHEN @I=4 THEN 'QUATRE'  
  938.    WHEN @I=5 THEN 'CINQ'  
  939.    WHEN @I=6 THEN 'SIX'
  940.    WHEN @I=7 THEN 'SEPT'  
  941.    WHEN @I=8 THEN 'HUIT'  
  942.    WHEN @I=9 THEN 'NEUF'
  943.    WHEN @I=10 THEN 'DIX'  
  944.    WHEN @I=11 THEN 'ONZE'  
  945.    WHEN @I=12 THEN 'DOUZE'
  946.    WHEN @I=13 THEN 'TREIZE'  
  947.    WHEN @I=14 THEN 'QUATORZE'  
  948.    WHEN @I=15 THEN 'QUINZE'
  949.    WHEN @I=16 THEN 'SEIZE'  
  950.    WHEN @I=17 THEN 'DIX-SEPT'  
  951.    WHEN @I=18 THEN 'DIX-HUIT'
  952.    WHEN @I=19 THEN 'DIX-NEUF'
  953. END
  954.  
  955. RETURN @RETVAL
  956.  
  957. END
  958.  
  959. GO
  960.  
  961.  
  962.  
  963. CREATE FUNCTION F_NEC_100 (@I INT)
  964.    RETURNS VARCHAR(32)
  965. AS
  966.  
  967. /******************************************************************************
  968. * Frédéric BROUARD - SQLpro - 2004-08-07                                      *
  969. * Sous procédure de transformation des nombres de 20 à 99 en littéraux        *
  970. ******************************************************************************/
  971.  
  972. BEGIN
  973.  
  974. DECLARE @RETVAL VARCHAR(256)
  975.  
  976. IF NOT(@I BETWEEN 20 AND 99)
  977.    RETURN @RETVAL
  978.  
  979. DECLARE @U  CHAR(1)
  980.  
  981. SET @U = SUBSTRING(CAST(@I AS CHAR(2)), 2, 1)
  982.  
  983. SET @RETVAL =
  984.  
  985. CASE
  986.    WHEN @I = 20 THEN 'VINGT'
  987.    WHEN @I = 21 THEN 'VINGT ET UN'
  988.    WHEN @I BETWEEN 22 AND 29 THEN 'VING-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  989.  
  990.    WHEN @I = 30 THEN 'TRENTE'
  991.    WHEN @I = 31 THEN 'TRENTE ET UN'
  992.    WHEN @I BETWEEN 32 AND 39 THEN 'TRENTE-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  993.  
  994.    WHEN @I = 40 THEN 'QUARANTE'
  995.    WHEN @I = 41 THEN 'QUARANTE ET UN'
  996.    WHEN @I BETWEEN 42 AND 39 THEN 'QUARANTE-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  997.  
  998.    WHEN @I = 50 THEN 'CINQUANTE'
  999.    WHEN @I = 51 THEN 'CINQUANTE ET UN'
  1000.    WHEN @I BETWEEN 52 AND 59 THEN 'CINQUANTE-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  1001.  
  1002.    WHEN @I = 60 THEN 'SOIXANTE'
  1003.    WHEN @I = 61 THEN 'SOIXANTE ET UN'
  1004.    WHEN @I BETWEEN 62 AND 69 THEN 'SOIXANTE-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  1005.  
  1006.    WHEN @I = 70 THEN 'SOIXANTE-DIX'
  1007.    WHEN @I = 71 THEN 'SOIXANTE ET ONZE'
  1008.    WHEN @I BETWEEN 72 AND 79 THEN 'SOIXANTE-' + dbo.F_NEC_20(CAST(@U AS INTEGER)+10)
  1009.  
  1010.    WHEN @I = 80 THEN 'QUATRE-VINGT'
  1011.    WHEN @I BETWEEN 81 AND 89 THEN 'QUATRE-VINGT-' + dbo.F_NEC_20(CAST(@U AS INTEGER))
  1012.    WHEN @I BETWEEN 90 AND 99 THEN 'QUATRE-VINGT-' + dbo.F_NEC_20(CAST(@U AS INTEGER)+10)
  1013.  
  1014. END
  1015.  
  1016. RETURN @RETVAL
  1017.  
  1018. END
  1019.  
  1020. GO
  1021.  
  1022. CREATE FUNCTION F_NEC_0_100 (@I INT)
  1023.    RETURNS VARCHAR(256)
  1024. AS
  1025.  
  1026. /******************************************************************************
  1027. * Frédéric BROUARD - SQlpro - 2004-08-07                                      *
  1028. * Sous procédure de transformation des nombres de 0 à 100 en littéraux        *
  1029. ******************************************************************************/
  1030.  
  1031. BEGIN
  1032.  
  1033. IF @I = 0 RETURN 'ZÉRO'
  1034.  
  1035. IF @I BETWEEN 1 AND 19 RETURN dbo.F_NEC_20 (@I)
  1036.  
  1037. IF @I BETWEEN 20 AND 99 RETURN dbo.F_NEC_100 (@I)
  1038.  
  1039. IF @I = 100 RETURN 'CENT'
  1040.  
  1041. RETURN ''
  1042.  
  1043. END
  1044.  
  1045. GO
  1046.  
  1047.  
  1048. CREATE FUNCTION F_NOMBRE_EN_CHIFFRE (@I INTEGER)
  1049.    RETURNS VARCHAR(256)
  1050. AS
  1051.  
  1052. /******************************************************************************
  1053. * Frédéric BROUARD - SQLpro - 2004-08-07                                      *
  1054. * Fonction de transformation des nombres de 0 à 2 147 483 647 en littéraux    *
  1055. ******************************************************************************/
  1056.  
  1057. BEGIN
  1058.  
  1059. IF @I IS NULL RETURN NULL
  1060.  
  1061. DECLARE @SIGN VARCHAR(5)
  1062.  
  1063. IF @I < 0
  1064. BEGIN
  1065.    SET @SIGN = 'MOINS'
  1066.    SET @I = -1 * @I
  1067. END
  1068.  
  1069. IF @I BETWEEN 0 AND 100 RETURN COALESCE(@SIGN + ' ', '') + dbo.F_NEC_0_100 (@I)
  1070.  
  1071. -- le nombre est supérieur à 100
  1072.  
  1073. DECLARE @IS VARCHAR(10)
  1074. SET @IS = CAST(@I AS VARCHAR(10))
  1075.  
  1076. WHILE LEN(@IS) < 10
  1077.    SET @IS = '0'+@IS
  1078.  
  1079. DECLARE @D11 INT              -- chiffres des unité et dizaine
  1080. DECLARE @D100 INT             -- chiffre des centaines
  1081. DECLARE @D1000 INT            -- chiffre des milliers
  1082. DECLARE @D10000 INT           -- chiffre des dix-milliers
  1083. DECLARE @D100000 INT          -- chiffre des cent-milliers
  1084. DECLARE @D1000000 INT         -- chiffre des millions
  1085. DECLARE @D10000000 INT        -- chiffre des dix-millions
  1086. DECLARE @D100000000 INT       -- chiffre des cent-millions
  1087. DECLARE @D1000000000 INT      -- chiffre des milliards
  1088.  
  1089. SET @D11 = CAST(SUBSTRING(@IS, 9, 2) AS INTEGER)
  1090. SET @D100 = CAST(SUBSTRING(@IS, 8, 1) AS INTEGER)
  1091. SET @D1000 = CAST(SUBSTRING(@IS, 7, 1) AS INTEGER)
  1092. SET @D10000 = CAST(SUBSTRING(@IS, 6, 1) AS INTEGER)
  1093. SET @D100000 = CAST(SUBSTRING(@IS, 5, 1) AS INTEGER)
  1094. SET @D1000000 = CAST(SUBSTRING(@IS, 4, 1) AS INTEGER)
  1095. SET @D10000000 = CAST(SUBSTRING(@IS, 3, 1) AS INTEGER)
  1096. SET @D100000000 = CAST(SUBSTRING(@IS, 2, 1) AS INTEGER)
  1097. SET @D1000000000 = CAST(SUBSTRING(@IS, 1, 1) AS INTEGER)
  1098.  
  1099. DECLARE @RETVAL VARCHAR(256)
  1100. SET @RETVAL = ''
  1101.  
  1102. -- traitement des milliards
  1103. IF @D1000000000 <> 0
  1104.    SET @RETVAL = @RETVAL + dbo.F_NEC_20 (@D1000000000)+' MILLIARD'
  1105.  
  1106.  
  1107. -- traitement des millions
  1108. IF @D100000000 = 1
  1109.    SET @RETVAL = @RETVAL + ' CENT'
  1110. IF @D100000000 > 1
  1111.    SET @RETVAL = @RETVAL + ' ' + dbo.F_NEC_20 (@D100000000) + ' CENT'
  1112. -- exception de l's à la centaine pure
  1113. IF @D100000000 > 1 AND @D10000000 + @D1000000 = 0
  1114.    SET @RETVAL = @RETVAL + 'S'
  1115. IF @D10000000 * 10 + @D1000000 <> 0
  1116.    SET @RETVAL =  @RETVAL + ' ' + dbo.F_NEC_0_100 (@D10000000 * 10 + @D1000000)
  1117. IF @D100000000 * 100 + @D10000000 * 10 + @D1000000 <> 0
  1118.    SET @RETVAL = @RETVAL + ' MILLION'
  1119.  
  1120. -- traitement des milliers
  1121. IF @D100000 = 1
  1122.    SET @RETVAL = @RETVAL + ' CENT'
  1123. IF @D100000 > 1
  1124.    SET @RETVAL = @RETVAL + ' ' + dbo.F_NEC_20 (@D100000) + ' CENT'
  1125. -- exception de l's à la centaine pure
  1126. IF @D100000 > 1 AND @D10000 = 0 AND @D1000 = 0
  1127.    SET @RETVAL = @RETVAL + 'S'
  1128. IF @D10000 * 10 + @D1000 <> 0
  1129.    SET @RETVAL = @RETVAL + ' ' + dbo.F_NEC_0_100 (@D10000 * 10 + @D1000)
  1130. IF @D100000 * 100 + @D10000 * 10 + @D1000 <> 0
  1131.    SET @RETVAL = ' ' + @RETVAL + ' MILLE'
  1132.  
  1133. -- traitement des centaines
  1134. IF @D100 > 1
  1135.    SET @RETVAL = @RETVAL + ' ' +  dbo.F_NEC_20 (@D100)
  1136. IF @D100 > 0
  1137.    SET @RETVAL = @RETVAL + ' CENT'
  1138. -- exception de l's à la centaine pure
  1139. IF @D100 > 1 AND @D11 = 0
  1140.    SET @RETVAL = @RETVAL + 'S'
  1141. IF @D11 <> 0
  1142.    SET @RETVAL = @RETVAL + ' ' + dbo.F_NEC_0_100(@D11)
  1143.  
  1144. RETURN COALESCE(@SIGN + ' ', '') + LTRIM(@RETVAL)
  1145.  
  1146. END
  1147.  
  1148. GO
  1149.  
  1150. -- exemple d'utilisation
  1151. SELECT dbo.F_NOMBRE_EN_CHIFFRE(-2111623500) AS NOMBRE_EN_LETTRE
  1152.  
  1153. CREATE FUNCTION F_STORAGE_SIZE_KB (@TABLE_NAME VARCHAR(128), @NATURE CHAR(1))
  1154.    RETURNS BIGINT
  1155. AS
  1156.  
  1157. /******************************************************************************
  1158.   Obtention de la taille des informations stockées pour une table spécifique
  1159. *******************************************************************************
  1160.   Frédéric BROUARD - SQLpro - 2004-08-11
  1161. *******************************************************************************
  1162.   ATTENTION, ces données sont basées sur les informations contenues dans les
  1163.   tables systèmes de descriptions des fichiers. Ces tables ne sont pas
  1164.   forcément toujours à jour au moment de l'exécution de la fonction.
  1165.   Pour s'assurer de l'exactitude des informations, veuillez procéder
  1166.   préalablement à l'exécution de la commande DBCC UPDATEUSAGE pour la table
  1167.   spécifiée
  1168. *******************************************************************************
  1169.   FONCTIONNEMENT : (exemples)
  1170.   1)  SELECT dbo.F_STORAGE_SIZE_KB ('MaTable', 'I')
  1171.        => donne le cout de stockage en kilo octets des index de la table
  1172.           MaTable
  1173.   2)  SELECT dbo.F_STORAGE_SIZE_KB ('MaTable', 'D')
  1174.        => donne le cout de stockage en kilo octets des données de la table
  1175.           MaTable
  1176.   3)  SELECT dbo.F_STORAGE_SIZE_KB ('MaTable', '')
  1177.       => donne le cout de stockage en kilo octets des données et index de la
  1178.          table MaTable
  1179. ******************************************************************************/
  1180.  
  1181. BEGIN
  1182.  
  1183. IF @TABLE_NAME IS NULL
  1184.    RETURN NULL
  1185.  
  1186. IF NOT EXISTS(SELECT *
  1187.               FROM INFORMATION_SCHEMA.TABLES
  1188.               WHERE TABLE_NAME = @TABLE_NAME
  1189.                 AND TABLE_TYPE = 'BASE TABLE')
  1190.    RETURN NULL
  1191.  
  1192. DECLARE @id       INT
  1193. DECLARE @DATA_SIZE BIGINT
  1194. DECLARE @INDX_SIZE BIGINT
  1195.  
  1196. SELECT @id = id
  1197. FROM   sysobjects
  1198. WHERE  name = @TABLE_NAME
  1199.  
  1200. IF @id IS NULL
  1201.    RETURN NULL
  1202.  
  1203. SET @DATA_SIZE = 0
  1204. SET @INDX_SIZE = 0
  1205.  
  1206. -- la taille des données d'un objet  
  1207.    SET @DATA_SIZE = (SELECT SUM(dpages)
  1208.                      FROM   sysindexes
  1209.                      WHERE  indid < 2
  1210.                        AND  id = @id)
  1211.                   + (SELECT COALESCE(SUM(used), 0)
  1212.                      FROM   sysindexes
  1213.                      WHERE  indid = 255
  1214.                        AND  id = @id)
  1215.  
  1216. -- la taille des index d'un objet
  1217.    SET @INDX_SIZE = (SELECT SUM(used)
  1218.                      FROM   sysindexes
  1219.                      WHERE  indid IN (0, 1, 255)
  1220.                      AND  id = @id)
  1221.                   - @DATA_SIZE
  1222.  
  1223. IF @NATURE = 'I' SET @DATA_SIZE = 0
  1224. IF @NATURE = 'D' SET @INDX_SIZE = 0
  1225.  
  1226. -- cooroboration avec les unités de mesure physique de stockage du système
  1227. SELECT @DATA_SIZE = (@DATA_SIZE + @INDX_SIZE) * low / 1024.0
  1228. FROM   master.dbo.spt_values
  1229. WHERE  NUMBER = 1
  1230.   AND  TYPE = 'E'
  1231.  
  1232. RETURN @DATA_SIZE
  1233.  
  1234. END
  1235.  
  1236. GO
  1237.  
  1238. CREATE FUNCTION F_DROP_CHARS (@DATA VARCHAR(128), @CHARS_TO_DROP VARCHAR(220))
  1239.    RETURNS VARCHAR(128)
  1240. AS
  1241.  
  1242. /******************************************************************************
  1243.   Suppression de caractères dans une chaine de caractères
  1244. *******************************************************************************
  1245.   Frédéric BROUARD - SQlpro - 2004-08-11
  1246. *******************************************************************************
  1247.   La fonction F_DROP_CHARS supprime tous les caractères contenus dans la
  1248.   chaine de caractères @CHARS_TO_DROP au sein de la chaine @DATA.
  1249.   Exemple : SELECT dbo.F_DROP_CHARS('Locomotive', 'lot')
  1250.             => Lcmive
  1251. ******************************************************************************/
  1252.  
  1253. BEGIN
  1254.  
  1255. IF @DATA IS NULL OR @CHARS_TO_DROP IS NULL
  1256.    RETURN NULL
  1257.  
  1258. IF @DATA = ''
  1259.    RETURN ''
  1260.  
  1261. IF @CHARS_TO_DROP = ''
  1262.    RETURN @DATA
  1263.  
  1264. DECLARE @NEW_DATA VARCHAR(128)
  1265. SET @NEW_DATA = ''
  1266. DECLARE @I INT
  1267. SET @I = 1
  1268. DECLARE @C CHAR(1)
  1269.  
  1270. WHILE @I <= LEN(@DATA)
  1271. BEGIN
  1272.    SET @C = SUBSTRING(@DATA, @I, 1)
  1273.    IF CHARINDEX(@C, @CHARS_TO_DROP) = 0
  1274.       SET @NEW_DATA = @NEW_DATA + @C
  1275.    SET @I = @I +1
  1276. END
  1277.  
  1278. RETURN @NEW_DATA
  1279.  
  1280. END
  1281.  
  1282. GO
  1283.  
  1284. CREATE VIEW V_DATEHEURE_COURANTE
  1285. AS
  1286.    SELECT CURRENT_TIMESTAMP AS DATEHEURE_COURANTE
  1287. GO
  1288.  
  1289. CREATE FUNCTION F_DATE_HEURE_FORMAT_COMPACT ()
  1290.    RETURNS CHAR(16)
  1291. AS
  1292.  
  1293. /******************************************************************************
  1294.   Obtention d'une dateheure au format compact AAAAMMJJHHMMSS
  1295. *******************************************************************************
  1296.   Frédéric BROUARD - sqlpro - 2004-08-11
  1297. *******************************************************************************
  1298.   NOTA : cette fonction est basée sur la vue V_DATEHEURE_COURANTE implémentée
  1299.   ci dessus.
  1300. ******************************************************************************/
  1301.  
  1302. BEGIN
  1303. DECLARE @DH CHAR(16)
  1304.  
  1305. SELECT @DH = CONVERT(CHAR(8), DATEHEURE_COURANTE, 112)
  1306.            + REPLACE(CONVERT(CHAR(8), DATEHEURE_COURANTE, 108), ':', '')
  1307. FROM V_DATEHEURE_COURANTE
  1308.  
  1309. RETURN @DH
  1310. END
  1311.  
  1312. GO
  1313.  
  1314. /******************************************************************************
  1315.   Recherche de correspondance de chaîne basé sur les expressions régulières
  1316. *******************************************************************************
  1317.   Frédéric BROUARD - sqlpro - 2004-08-11
  1318. *******************************************************************************
  1319.   NOTA : cette se comporte à la manière du prédicat SIMILAR de la norme
  1320.          SQL:1999 qui simule un REGEX. Elle utilise des appels OLE à un REGEX
  1321.          écrit en VB script et disponible sur toutes les éditions de Windows
  1322. ******************************************************************************/
  1323.  
  1324. CREATE FUNCTION dbo.F_REGEXMATCH (@MOTIF   VARCHAR(256),
  1325.                                   @VALEUR  VARCHAR(8000),
  1326.                                   @NOCASSE BIT)
  1327.  
  1328. RETURNS INT
  1329. AS
  1330. BEGIN
  1331.        DECLARE @OLE_OBJECT INT, @RETVAL INT, @MATCH bit
  1332.        SET @MATCH=0
  1333.        EXEC @RETVAL=sp_OACreate 'VBScript.RegExp',@OLE_OBJECT OUT
  1334.        IF (@RETVAL <> 0) RETURN NULL
  1335.        EXEC @RETVAL=sp_OASetProperty @OLE_OBJECT, 'Pattern', @MOTIF
  1336.        IF (@RETVAL <> 0) RETURN NULL
  1337.        EXEC @RETVAL=sp_OASetProperty @OLE_OBJECT, 'IgnoreCase', @NOCASSE
  1338.        IF (@RETVAL <> 0) RETURN NULL
  1339.        EXEC @RETVAL=sp_OAMethod @OLE_OBJECT, 'Test',@MATCH OUT, @VALEUR
  1340.        IF (@RETVAL <> 0) RETURN NULL
  1341.        EXEC @RETVAL=sp_OADestroy @OLE_OBJECT
  1342.        RETURN @MATCH
  1343. END
  1344.  
  1345. GO
  1346.  
  1347. -- exemple : liste de tous les clients dont le nom commence avec la lettre "g"
  1348. -- avec respect de la casse
  1349.  
  1350. SELECT *
  1351. FROM   DB_HOTEL.dbo.T_CLIENT
  1352. WHERE  dbo.F_REGEXMATCH('g.*', CLI_NOM, 0) <> 0
  1353.  
  1354. -- sans respect de la casse
  1355. SELECT *
  1356. FROM   DB_HOTEL.dbo.T_CLIENT
  1357. WHERE  dbo.F_REGEXMATCH('g.*', CLI_NOM, 1) <> 0

Télécharger

Documents à télécharger

Revenir en haut