Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: varchar column check

    Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.

    I tried using isnumeric, but it also allows those with periods, comma's etc.
    Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.

    As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.

    I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...

  2. #2
    Join Date
    Mar 2004
    How about this...

    create table #temp(col varchar(20))

    insert into #temp(col) values(' 444')
    insert into #temp(col) values(' A 444')
    insert into #temp(col) values('.444')
    insert into #temp(col) values('123456')
    insert into #temp(col) values(' 5 444')
    insert into #temp(col) values(' 77-444')
    insert into #temp(col) values('123.')
    insert into #temp(col) values('aaaa 444')
    insert into #temp(col) values('t444')

    select col from #temp

    select convert(int, ltrim(col)) from #temp
    where ltrim(col) not like '%[^0-9]%'

    drop table #temp

  3. #3
    Join Date
    Feb 2004
    ofcourse! thanx for the chop-o-tree!

Posting Permissions

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