small query help me out

Question

Post reply

small query help me out

Viewing 7 posts - 16 through 21 (of 21 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is some test code I just wrote. See if this helps you figure out what you need to do.

create table #TestTable (

OrderNo varchar(13)

);

insert into #TestTable

select '000609876159' union all

select '0A0012345678';

select

*

from

#TestTable

where

OrderNo like '%[^0-9]%';

drop table #TestTable;

😎

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Chris (10/27/2008)
adi i tried that but u know what its not showing up 000609876159 in not like but its totally numeric.....

I’m sorry, but I don’t understand. Do you want to see strings that begin with leading zeros or don’t you? In any case here is a small script that tests many cases. If there

Is a case that I didn’t think about, can you post the insert statement that will cause the wrong row to return with the select statement?

create table test (vc varchar(50))

go

insert into test

select '1234'

union select '1 234'

union select '12

34'

union select '12a34'

union select '1234.'

union select '1234()'

union select '1.23.4'

union select '01234'

union select null

union select ''

union select char(10)

union select char(13)

union select '

'

go

select vc

from test

where vc not like '%[^0-9]%' and vc <> ''

--If you want to see strings with leading numbers

--remark the line bellow.

and vc not like '0%'

go

drop table test

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Chris (10/27/2008)
select isnumeric('3edf2') when i tried this it showing as 0 ...wats the reason for this

3edf2 cannot be cast to any of the numeric data types. It is not a number even by the loosest definition.

Edit: Maybe I should add 'if using base-10.'

why is isnumeric('3d2') showing as 1

3d2 can be successfully cast to float, although not any of the other number data types. It means 300. Hence it is classified as numeric and isnumeric will return 1 for it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chinna SSCertifiable Points: 5408 More actions · Answer 4

Thanks evryone ....and THANKS Gail i got it now...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Chris (10/27/2008)
Jeff...thkz for the response could plz clarify this....
so how should i consider it .....
select isnumeric(order_number+'.do') from tbl1..
is this the syntax...
i dont understand this query
SELECT ISNUMERIC('3D2'+'.d0'), ISNUMERIC('3E2'+'.d0'), ISNUMERIC('1,000'+'.d0'), ISNUMERIC(CHAR(13)+'.d0'), ISNUMERIC(CHAR(9)+'.d0')
when i execute all the output is zero but here how abt 1000..could u plz explain a bit more ....

That's because you used an "oh" instead of a zero in the query you wrote and in the query I wrote, they're supposed to be all zeros because none of the values have digits only. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christo Wolmarans Ten Centuries Points: 1170 More actions · Answer 6

Hi

Thanks for the posts Jeff and Gail. That is really interesting.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Thanks for the feedback, Christo. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)