select query- invalid characters

Question

select query- invalid characters

sindhigrl

Default port

Points: 1448
More actions
July 27, 2006 at 10:41 am

#91673

I am trying to pull a mailing list and my query is not working.
Select addressno, addressstreet, addresscity, addresszip from addresstbl

The problem is that some of the addressno are blank, or have spaces or invalid characters. Can someone tell me how to pull only the records with actual house numbers in the addressno field? I can't say:
Select addressno, addressstreet, addresscity, addresszip from addresstbl
where addressno is null
because it doesn't account for all the blanks or invalid characters.

Thanks!

Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sindhigrl Default port Points: 1448 More actions · Answer 1

Another thing I forgot to mention was that the reason I can't use left(addressno,1) and check for a number is because some addresses have a space(blank) before the actual number. This data really needs to be cleaned up.

Thanks!

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

Try LEFT(LTRIM(addressno),1) .

Of course, that only lets you know about the very first non-blank character.

sindhigrl Default port Points: 1448 More actions · Answer 3

Thanks! I actully tried that this morning, but then I still got some addresses that looked like 25p49 which we know are not valid. I guess I'll have to clean up as much as I can.

Thanks!

william wilson Grasshopper Points: 22 More actions · Answer 4

If you are using SQL Server 2005 you could create a CLR stored procedure to perform RegExMatch and only match [0-9]+.

Purushotham Kumar Katakam SSC Enthusiast Points: 172 More actions · Answer 5

Hi,

This function may helpful u to retrieve the data without cleansing the data.

Create FUNCTION INTCHECK

(@addressno varchar(20)) returns integer

as

begin

declare @intIncr integer , @bool integer

set @intIncr = 1

while @intIncr < = len(@addressno)

Begin

if not (ascii(substring(ltrim(rtrim(@addressno)),@intIncr,1 )) > = 48

AND ascii(substring(ltrim(rtrim(@addressno)),@intIncr,1 )) < = 57)

Begin

set @addressno = '0'

end

set @intIncr = @intIncr + 1

end

return @addressno

end

Please Create this procedure in your Database, after creation of this procedure U can run the statement like

select addressno, addressname from addresstbl where (select dbo.intcheck(addressno)) > 0

I hope this may solve ur problem.

Thanks & Regards,

Kumar KP.

Thanks & Regards,
9989069383
Katakam.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

Here are some suggestions.

1. Use the ISNUMERIC function to check the addressno column.

SELECT

addressno, addressstreet, addresscity, addresszip FROM (

SELECT LTRIM(RTRIM(addressno)) AS addressno, addressstreet, addresscity, addresszip

FROM addresstbl

) x

WHERE ISNUMERIC(addressno) = 1

2. Look for non-numeric characters with LIKE.

WHERE LEN(addressno) > 0 AND NOT(addressno LIKE '%[^0-9]%')

3. Parse out the first word of a complete address line to check for a numeric street number.

SELECT addressno, addressstreet, addresscity, addresszip FROM (

SELECT LTRIM(RTRIM(addressno)) AS addressno, CHARINDEX(' ', addressno) AS WordBoundary, addressstreet, addresscity, addresszip

FROM addresstbl

) x

WHERE ISNUMERIC(CASE WHEN WordBoundary = 0 THEN addressno ELSE LEFT(addressno, WordBoundary-1) END) = 1

nigel. SSChampion Points: 11636 More actions · Answer 7

Problem with ISNUMBERIC is that it will return 1 not only for integers but also for valid decimal and money values too, eg. '1.00', '1,000.00' , '£1.00' and '$1.00'

Nigel
How to post questions

sindhigrl Default port Points: 1448 More actions · Answer 8

Thanks for all the suggestions. I'm going to start going through them and seeing which one works best till I can get this data cleaned up.

Thanks!!

Anita

Thanks!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

Now that we've given you some ideas to work on, I'm going to suggest that you look for another approach. Cleaning addresses is very messy. If this is a one-time action, there are services that will clean them for you for a reasonable price. Unless you have to clean a huge and rapidly growing list of addresses to clean, I would strongly recommend paying to have it done. It will take a huge investment of your time to do it right, T-SQL queries alone aren't going to do it.

Did you know "W155N6362 HORSE CHESTNUT CT" is a valid address in Menomonee Falls WI? Or "39W452 W Mallory Dr" in Geneva IL? Then consider the fact that the data-entry miscreants are going to add an extra space here and there to make your life exiting. What query are you going to write to validate those street numbers? At the very least you're going to have to use regular expressions, either through CLR procedures or SSIS.