Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: What is the best way to filter string ?

    Hi All,

    By given the value of phone number "+(602)123456789/" , what is the best strategy to filter those special character and result in just purely integer, for this case, the value will be 602123456789 , i would like to filter Special character like +, (, ), / ..

    thank you for your guidance!

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    look at case and replace.

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    do i need a loop to parse it ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That would be more flexible but less efficient.

    Could there be any character in there? Is this unicode or ascii?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2004
    Posts
    74
    Sorry Pootle, how to differential unicode or ascii ?

    it should be unicode, this is sample data "+(602)123456789/

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Txt = "+(602)123456789/"

    NewTxt = replace(Txt,"+","")
    NewTxt = replace(NewTxt,"(","")
    NewTxt = replace(NewTxt,")","")
    NewTxt = replace(NewTxt,"/","")
    NewTxt = replace(NewTxt,".","")
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE	@Value NVARCHAR(20)
    
    SET	@Value = '+(602)123456789/'
    
    WHILE @Value LIKE '%[^0-9]%'
    	SET	@Value = STUFF(@Value, PATINDEX('%[^0-9]%', @Value), 1, '')
    
    SELECT	@Value AS theOriginalValue,
    	CAST(@Value AS BIGINT) AS theBIGINT

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    A little more optimized

    Code:
    DECLARE	@Value NVARCHAR(200)
    
    SET	@Value = 'a+(6aaaaaaaaaaa02...............()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffffffffffffffffffffffffffffff'
    
    WHILE @Value LIKE '%[^0-9]%'
    		SET	@Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')
    
    SELECT	@Value AS theOriginalValue,
    	CAST(@Value AS BIGINT) AS theBIGINT

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ugly as hell, but as efficient as it can be I would think (assuming it is ASCI). Alvincks - ASCI is CHAR and VARCHAR (i.e. 255 possible characters). Unicode is NCHAR and NVARCHAR (65k possible characters)

    Code:
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([myCol], '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '    ', ''), '
    ', ''), '', ''), '', ''), '
    ', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), ' ', ''), '!', ''), '"', ''), '#', ''), '$', ''), '%', ''), '&', ''), '''', ''), '(', ''), ')', ''), '*', ''), '+', ''), ',', ''), '-', ''), '.', ''), '/', ''), ':', ''), ';', ''), '<', ''), '=', ''), '>', ''), '?', ''), '@', ''), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', ''), '[', ''), '\', ''), ']', ''), '^', ''), '_', ''), '`', ''), 'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', ''), 'g', ''), 'h', ''), 'i', ''), 'j', ''), 'k', ''), 'l', ''), 'm', ''), 'n', ''), 'o', ''), 'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''), 'u', ''), 'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', ''), '{', ''), '|', ''), '}', ''), '~', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '�', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '�', ''), '', ''), '', ''), ' ', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '*', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', ''), '', '')
    Last edited by pootle flump; 11-12-08 at 04:27.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    THIS was your 10K post?!

    Oh dear
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DECLARE    @s AS VARCHAR(MAX)
    
    SELECT    @s    = 'REPLACE(' + COALESCE(@s, '[myCol]') + ', ''' + REPLACE(CHAR(number), '''', '''''') + ''', '''')'
    FROM    dbo.numbers
    WHERE    number BETWEEN 0 AND 255
            AND number NOT BETWEEN 48 and 57
    
    PRINT    @s
    Lots of chars don't paste in DBF. You probably won't need 80+% of them either.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    THIS was your 10K post?!
    Ah crap - I forgot

    BTW - you know I mentioned another idea for the swear words: it's the same as this
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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