Help with selecting certain characters

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with selecting certain characters

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-04-22 : 20:47:47

Hi,

I am currently selecting against a database which has quite a bit of data in it that hasnt been validated. In the database field "city" I have many cities that have been entered as "#$83Ssff$$".

What I would like to do is select only cities that are filled in with
letters [A-Z]

Does anybody have an idea how I would do this?

Thanks alot

Mike123



CREATE PROCEDURE dbo.select_Cities_Distinct 
	 
AS SET NOCOUNT ON

SELECT DISTINCT City from tblUserdetails

GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-22 : 21:11:42

[code]~~where City LIKE '%[a-z]'~~[/code]

Edit: Not going to work for you here.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-04-22 : 21:40:52

Hi Ehorn,

That works pretty good, eliminates most of my problems. I thought it completely worked at first but I was wrong. My problem is that I am saving all these records as filenames, but certain characters are messing it up.

What I need to do is bring back each city, and where there are only A-Z and spaces for each character in the string. Any idea on how I could accomplish this?

Thanks alot for the help

Mike123

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-22 : 23:22:00

[code]
--create tally table
set nocount on
create table dbo.numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255 --max length of input parameter
begin
insert into numbers
select @n
set @n = @n + 1
end
GO

select City
from
(
select
City ,
case when ((a between 65 and 90) or (a between 97 and 122) or a = 32) then 1 else 0 end as valid
from
(
select City , ascii(substring(City,n,1)) a
from tblUserdetails, numbers
where n <= len(City)
) d1
) d2
where valid = 1
group by city
having count(valid) = len(city)[/code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-04-25 : 23:46:14

Thanks very much ehorn! worked perfectly!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources