filter out ? in string - SQL Server Forums

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)

filter out ? in string

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-04 : 18:43:02

Hi,

I have the following SPROC which works as I wish except for the fact that there is one record that still comes back with a ? row. I am using this data for file paths so can only have a-z, 0-9.

How can I modify this SPROC to make sure the "?" does not come back?

CREATE PROCEDURE dbo.select_Cities_Distinct

AS SET NOCOUNT ON

--create tally table
--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

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) order by city

GO

Thanks alot!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 18:44:56

What do you want in place of the ? or do you just want that entire row removed?

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-04 : 20:03:21

Hi Tara,

Just not including the row at all would be great. I actually ran into another problem and need to adjust it a bit further.

I don't want cities included in the list if they don't have at least one row with

"active = 1 "

My sproc is selecting cities with no active users (only inactive) and its messing things up.

If you have an idea on that too thats a huge help!

Thanks again Tara!

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 20:05:33

So can't you just exclude these in the WHERE clause? WHERE SomeColumn NOT LIKE '%?%'

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-04 : 20:16:07

Isn't my ascii check supposed to limit it to a-z, 0-9 ?

Sorry I've never done this before its probably something like you suggest but Im not confident on how to integrate it.

Thanks again

mike123

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 06:20:02

CREATE PROCEDURE dbo.select_Cities_Distinct

AS SET NOCOUNT ON

select City
from tblUserdetails join (select t*8*8+t1*8+t2 n from (select 0 t union select 1 union select 2 union select 3) a,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7) b,
(select 1 t2 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8) c) t
on n <= len(City)
and valid = 1
group by city
having sum(case when substring(City,n,1) between 'A' and 'Z' or substring(City,n,1) between 'a' and 'z' or
substring(City,n,1) between '0' and '9' or substring(City,n,1)=' ' then 1 else 0 end) = len(city)
order by city
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 06:22:52

emmm....
wouldn't this be much simpler:
where City not like '%[^a-z^0-9]%'

Go with the flow & have fun! Else fight the flow

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 06:27:49

^) you right

but

where City like '%[^a-z^0-9]%'

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 06:33:00

emmm... not working... '?' is passed

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 06:35:12

select City
from tblUserdetails
where City not like '%[^a-z^0-9^ ]%'

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-05 : 13:51:46

ha thanks guys for all the answers...

I've left myself with one final problem that has to do with the data thats in the database.

Some cities have been entered with a blank space in front of their names as seen below. How can I trim the beggining blank spaces of my results? Not all spaces, but just the beginning.

Andover
BAYONNE
Beauce
Belmont
Bradenton
Calgary
Canton
death valley
fairland
graham

Thanks alot once again!

Mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-05 : 13:53:02

Use LTRIM for this.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-10-05 : 13:56:51

that was nice and easy

thanks tara :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources