is string
-
is anyone aware of a function that identifies if a value in a column is a alphabetic value as opposed to a numeric value
i.e. select * from some_table where some_column is alpha?
-
Wouldn't select * from some_table where not isnumeric(some_column) give you what you were looking for?
-
Thanks Lynn you almost got it right but I was able to figure it out correct syntax is as follows:
select subs_no from some_table where isnumeric(some_column) = 0
Reverse logic nice
-
Thanks Lynn you almost got it right but I was able to figure it out correct syntax is as follows:
select subs_no from some_table where isnumeric(some_column) = 0
Reverse logic nice
-
ISNUMERIC is not always reliable. I would suggest you do a search on that within this site.
Try the following:
SELECT ISNUMERIC( 1)
SELECT ISNUMERIC( 'A')
SELECT ISNUMERIC( '-')
I wasn't born stupid - I had to study.
-
>>if a value in a column is a alphabetic value
How do you define "alphabetic value" ?
Just the upper & lowercase letters a to z ?
What about punctuation, periods, commas, exclamation points ?
You could use PatIndex() to either check that a certain range of characters exist in the column, or check that a certain range don't exist.
eg This will return non-zero for patindex because of the exclamation point:
Select 'I found a non-alpha character'
Where Patindex( '%[^a-z ]%', 'This is a string ! Isn''t it ?') > 0
-
It might be easier to use the ASCII character set from 49 to 57. Look up function ASCII(). You may need to use this in conjunction with PATINDEX().
I wasn't born stupid - I had to study.
-
select subs_no from some_table where (isnumeric(some_column) = 0) OR some_column IN ('+', '-')
-- i believe these are the only exemptions from the isnumeric function. If there are othere caracters, just include it in the IN list.
-
Interesting, SELECT ISNUMERIC('-') returns the value 1. If you do the following, you see why:
SELECT ISNUMERIC('-'), cast('-' as int)
The cast returns 0 (zero).
-
You gonna need to include also '123D4', '432E1', '$1234', '$4321' and all other possible variations.
_____________
Code for TallyGenerator -
Please see the following...
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646&p=2
Don't fight it folks... ISNUMERIC should never be used as ISALLDIGITS or vice versa.
--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)
Intro to Tally Tables and Functions -
-
Thanks for all your help -- Wow
-
You bet, guys... thanks for the feedback.
--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)
Intro to Tally Tables and Functions -
my 2 cents...
not isnumeric just means, it could be any other datatype other than numeric. Does not necessarily mean varchar. As an example try this on the table I am reating below
select * from t2 where isnumeric(f3) = 0
drop table t2
create table t2(f1 int, f2 varchar(10), f3 datetime)
insert into t2 values(111,'aaa',getdate())
insert into t2 values(222,'bbb',getdate()+1)
insert into t2 values(333,'ccc',getdate()+2)
This is what we may have to do, but be very careful while working with sys tables in your applications
select t2.f1, t2.f2, t2.f3 from t2
inner join syscolumns sc on sc.name = 'f1'
inner join sysobjects so on so.id = sc.id and so.name = 't2'
inner join systypes st on sc.UserType = st.UserType and st.Name = 'varchar'
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply