Solved

SQL Query For Alpha or Numerical Characters

Posted on 2003-11-26
9
1,423 Views
Last Modified: 2011-04-14
Hello,
I have a column in my database called 'Atozlisting', it is filled with only i character in each row of data.  the character maybe an alpha character or a Numeral.  i.e
a or g or f or 5 or 3 or j... etc

I'm trying to write two SQL statements.  The first one will return only records that have an alpha characters as their value and the second one will return records that have a numerals as their value

thanks
:-)
0
Comment
Question by:show_t
  • 5
  • 4
9 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 9825063
Try this

All together

select case when isnumeric(Atozlisting) = 1 then 'Num' else 'alpha' end
from yourtable

or

Select Atozlisting  --numeric
from yourtable
where isnumeric(Atozlisting) = 1

and

Select Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0
0
 

Author Comment

by:show_t
ID: 9825358
thanks for your help,

i've also tried adding 'DISTINCT' in the SQL query in an attempt to NOT return any records with the same values but this doesn't work.  is 'distinct' only used with joins ?

thanks again
0
 
LVL 6

Expert Comment

by:lausz
ID: 9826072
This must work.

Select distinct Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0


0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:show_t
ID: 9826227
Yeah it works just fine, i just wanted to know if you also knew how to NOT return records that have the same value

Thanks
0
 
LVL 6

Expert Comment

by:lausz
ID: 9826654
Do you need more help ?
0
 

Author Comment

by:show_t
ID: 9827842
yes please, about the 'Distinct' issue
0
 
LVL 6

Expert Comment

by:lausz
ID: 9831269
Can you post your final query...(with the distinct not running) ?
0
 

Author Comment

by:show_t
ID: 9832355
Hello,

Basically, when i do this :

Select distinct Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0

IT WORKS! i get :
A
A
A
B
B
C
F
G
G
J
K
L
M
M

However,  i need the query to only return :
A
B
C
F
G
J
K
L
M

Do you see what i mean, without duplicates.

thanks
:-)
0
 
LVL 6

Accepted Solution

by:
lausz earned 125 total points
ID: 9833025
I don't understand ..

Try this two options :

Select distinct ltrim(Atozlisting) -- alpha
from yourtable
where isnumeric(Atozlisting) = 0

or


Select Atozlisting -- alpha
from yourtable
where isnumeric(Atozlisting) = 0
group by Atozlisting
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question