Professional Web Applications Themes

"Cleanup" function - Microsoft SQL / MS SQL Server

I posted this question a while ago and got a great solution. Unfortunately, I have lost the code for it. I need to write a function that will accept an alphanumeric column and return it with all the non-numeric characters stripped out. i.e. select cleaner('A786%1') the result should be 7861. All non-numerics are gone. It can be returned in whatever format, I can deal with it from there. Any help is appreciated. Thanks, Scott...

Sponsored Links
  1. #1

    Default "Cleanup" function

    I posted this question a while ago and got a great solution. Unfortunately,
    I have lost the code for it.

    I need to write a function that will accept an alphanumeric column and
    return it with all the non-numeric characters stripped out.

    i.e. select cleaner('A786%1')
    the result should be 7861. All non-numerics are gone.
    It can be returned in whatever format, I can deal with it from there.

    Any help is appreciated.

    Thanks,

    Scott



    Sponsored Links
    Scott Guest

  2. #2

    Default Re: "Cleanup" function

    CREATE FUNCTION dbo.CleanChars
    (str VARCHAR(8000), validchars VARCHAR(8000))
    RETURNS VARCHAR(8000)
    BEGIN
    WHILE PATINDEX('%[^' + validchars + ']%',str) > 0
    SET str=REPLACE(str, SUBSTRING(str ,PATINDEX('%[^'
    + validchars +']%',str), 1) ,'')
    RETURN str
    END

    GO

    SELECT dbo.CleanChars('A786%1','0-9')

     
    Unfortunately, 

    Use Google to find your previous posts:

    http://groups.google.com/advanced_group_search



    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: "Cleanup" function

    Refer to:
    http://groups.google.com/groups?selm=%23Y%23uMEuxCHA.1268%40TK2MSFTNGP10

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Replies: 1
    Last Post: April 24th, 01:27 PM
  2. ListContains function sees "10" as "1"
    By blcott in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 1st, 12:26 PM
  3. CFINPUT type="radio" w/ "value" requires "label"
    By Iceborer in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 21st, 06:16 PM
  4. #26162 [NEW]: $a="0abcdefg";if ($a==0) echo "OK"; result is "OK" ?!
    By zhuminglun at yahoo dot com dot cn in forum PHP Development
    Replies: 0
    Last Post: November 7th, 12:04 PM
  5. "Start" "Program" "Menu" list is empty
    By Pete in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 10th, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139