query - find a alphabetic charther

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query - find a alphabetic charther

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-07-19 : 08:05:36

Morning/Afternoon

Hope you can help me, am current working on ISIN numbers it doesn’t matter if you don't know what they are.

Now I have a table that is like this

table name = tDocISIN
col1 = LID(int)
col2 = sISIN(varchar(50))
col3 = lDocumentId(int)

Which has sample data like this

84237 USX7318VAA45 43997395
92579 USO45167AR45 44021575
84244 USY636511146 44025820
84243 USY63651AA45 44025820
113633 USP51225AA11 44028865
92846 USG36912AG13 44029240
92847 US359860AG84 44029240

Now I want to run a select query that will only bring the ones that have ‘CA’ or ‘US’ as the first two characters.

I can do that by running this:

select * from tDocISIN
where sISIN like 'CA%'or sISIN like 'US%'

But my other where clause I want to add is only bring back the ones that have CA or US as the first two characters and any other character after the first two or in the rest on the string.

So US7636516745 is an ISIN I don’t want to bring back and US763TY16745 is an ISIN I do want to bring back.

The character that does exist after the first two characters will be anywhere within the rest of the string.

Hope that is clearly explain any help would be great.

Am a total beginner. Thanks in advance.

Lee

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-19 : 08:20:37

I think this is what you mean

select * from tDocISIN
WHERE
LEFT (sISIN,2) IN ('CA','US')
and
ISNUMERIC(RIGHT(sISIN,10)) = 0

IF sISIN isn't always 12 characters then you'll have to
ISNUMERIC(RIGHT(sISIN,LEN(sISIN)-2)) = 0

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 08:22:27

[code]AND (
SUBSTRING(sISIN, 3, 48) LIKE '%[A-Z]%'
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-20 : 01:48:51

quote:
Originally posted by jimf

I think this is what you mean

select * from tDocISIN
WHERE
LEFT (sISIN,2) IN ('CA','US')
and
ISNUMERIC(RIGHT(sISIN,10)) = 0

IF sISIN isn't always 12 characters then you'll have to
ISNUMERIC(RIGHT(sISIN,LEN(sISIN)-2)) = 0

Jim

Isnumerice is not always reliable

select data from
(
select '124' as data union all
select '12d2' as data union all
) T
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-20 : 01:58:53

[code]SELECT * FROM tDocISIN
WHERE sISIN LIKE 'CA%[a-z]%'
OR sISIN LIKE 'US%[a-z]%'[/code]
Peter Larsson
Helsingborg, Sweden

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-20 : 07:00:33

Yikes! That's disconcerting that ISNUMERIC isn't reliable, I wish I'd known that about a year ago!

Jim

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-07-20 : 09:53:23

"isnumeric" deals with scientific numbers as well.....well documented problem. you really need a REGEX fucntion to validate for numbers from 0-9...and maybe decimal places

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources