How to tell if a character exists

Question

Post reply

How to tell if a character exists

Ben-347858

Old Hand

Points: 391
More actions
September 1, 2006 at 10:43 am

#116672

I'm hoping someone can give me some direction on this.
The ERP Database we have contains a field named 'ShipToNum' that stores Ship ID's but it is a char data type. The issue I'm having is that there are certain records that contain an ID that might look like 'hug1' but the ID is supposed to be a number like 12.
I need to return records that only contain a "Real" Ship ID.
I've been doing a bunch of searches to find an answer but haven't come across anything yet. I thought I could use Contains() but it doesn't seem to work. And WildCard searches won't work either.
I would use the IsNumeric() Function but because the data type is 'char' it always returns false.
Does anyone have any good ideas on this?
Thanks,
Ben

Viewing 15 posts - 1 through 15 (of 16 total)

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

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

select *

from YourTable

where ShipToNum not like '%[^0-9]%'

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Do you know what the width of the Number is supposed to be (for example last 3 Charaters are ID etc)?

Does it have a specific character that seperates Characters and Number?

If you have this then its an easy query.

Thanks

Sreejith

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This works for me... maybe you can expand from that :

select

isnumeric(rtrim(ltrim('hug1 ')))

--0

select isnumeric(rtrim(ltrim('1 ')))

--1

Ben-347858 Old Hand Points: 391 More actions · Answer 4

unfortunately there isn't any type of consistency with the way the characters are stored.

so they could be 'hug1' '1hug' '1hu2g' etc...

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

Ben,

Try this.

Declare @s_Data varchar(50),

@s_ID varchar(50)

set @s_Data = '5ABC1238990'

set @s_ID =''

select @s_Data

If IsNumeric(Right(@s_Data,1)) =1

Begin

while IsNumeric(Right(@s_Data,1)) =1

Begin

set @s_ID = Cast(Right(@s_Data,1) as char(1)) + @s_ID

set @s_Data = left(@s_Data,len(@s_Data)-1)

End

select @s_Data,@s_ID

End

If this works create this as UDF and you can use it.

Thanks

Sreejith

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

What's wrong with the solution I provided?

What else do you need to do besides checking if it's a real id?

Do you need to extract it to another field, update the data??

Ben-347858 Old Hand Points: 391 More actions · Answer 7

RGR'us, unfortunately isNumeric() doesn't seem to work.

Our ERP system actually uses a progress database but it's supposed to be SQL-92 compliant. Most functions work as they would in SQL Server but for some reason isNumeric() throws an error.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ninja's_RGR'us

SSC Guru

Points: 294069

September 1, 2006 at 11:55 am

#658110

What error does it throw?

Ben-347858 Old Hand Points: 391 More actions · Answer 9

Just gives a syntax error. Even doing this will give that error:

SELECT IsNumeric(ShipToNum)

From Pub.ShipTo

Where CustNum = 10

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ok, can't help you much there... works fine on sql server.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 11

Don't forget that ISNUMERIC sometimes allows an "e" or "d" and will also allow decimal points and dollar signs and etc, etc....

The method Ken uses is nasty fast and is guaranteed to find only those things that have all digits... you can trim to keep leading spaces from being a problem (as you did).

[Edit] Ken's solution should also work fine in just about any RDBMS that allows rational expressions in LIKE clauses....

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Thanx for the info... I didn't know that about the IsNumeric Function .

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 13

That's one of the things I admire about you, Remi... absolute integrity and honesty...

Here's a couple of links I was involved with that explain it all...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1073&lngWId=5

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Thanx for the links. I had already met part of the problem with strings like ',' or ',' but that's about it. That would be a nice gotcha to put in the QOD... I'm curious to see how many DBA can get that answer right without running the code in QA .