[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Query For Alpha or Numerical Characters

Posted on 2003-11-26
9
Medium Priority
?
1,432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

650 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