Post reply

Ignore 0's & 9's from a column data

  • June 18, 2010 at 10:03 am

    #387186

    Hi Friends,

    I was trying to eliminate 0's & 9's for a validation and not successful in it.

    Basically i don't want to include say column X = 0's & 9's (varying length between 5 to 15) for a process. Since it's of varying length i can't put a check directly.

    Tried using LIKE [0%], [9%] but not working.

    Note: X will be having Alphanumeric & special char data... Only 0's & 9's to be ignored.

    Need your help / suggestion to solve this.

    Thanks in advance 🙂

    Cheers,

    Suresh

    Suresh

  • June 18, 2010 at 10:20 am

    #1182241

    Hopefully I am not over simplifying this but the following code would work assuming the data is as straight forward as my sample data.

    declare @sample table

    (string varchar(10))

    insert into @sample (string) values('12345680')

    insert into @sample (string) values('45690459')

    insert into @sample (string) values('a9s156f4')

    insert into @sample (string) values('sd59fs94')

    insert into @sample (string) values('s4d06s2d0')

    insert into @sample (string) values('sd4')

    insert into @sample (string) values('sd6540')

    insert into @sample (string) values('s6d5f4\')

    insert into @sample (string) values('s65d4')

    select * from @sample where

    string not like('%9%') and string not like('%0%')

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 18, 2010 at 10:28 am

    #1182244

    Thanks for the reply Dan...

    But i don' want to ignore below rows as they hav only ONE 0 & 9

    12345680

    45690459

    a9s156f4

    sd59fs94

    s4d06s2d0

    sd6540

    I want to ignore these only...

    insert into @sample (string) values('00000')

    insert into @sample (string) values('999999999999')

    Any suggestions?

    Suresh

  • June 18, 2010 at 10:31 am

    #1182246

    Just to clarify you want to ignore only if it is all 0 or all 9

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 18, 2010 at 10:33 am

    #1182251

    Yeah, you are right!

    Suresh

  • June 18, 2010 at 10:38 am

    #1182256

    assuming I have understood you correctly the following would eliminate any row that is all 0's or all 9's regardless of length.

    select * from @sample where

    string <> Replicate('0',len(string))

    and

    string <> Replicate('9',len(string))

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • June 18, 2010 at 12:23 pm

    #1182315

    Bang on!!!

    Thanks for your timely help 🙂

    Suresh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply