Updating when you have a space

Question

Updating when you have a space

psconsulting

SSC Eights!

Points: 944
More actions
October 4, 2002 at 8:29 am

#105175

I have to update a file I am working with where the address might look like the following:
123 54 first ave
1234 45 second ave
12 34 third ave
I need the result to look like this
123-54 first ave
1234-45 second ave
12-34 third ave
The only constant I have is that there is always two spaces before the first alpha character and what I need to figure out is in those cases where two spaces exist before the first alpha character, how do I get a hyphen in that first space?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 1

use combination of charindex, and subtring and replace

Get the substring of hte numbers and do the replace on these. Then contactenate that with the substring of the rest of the address.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

psconsulting SSC Eights! Points: 944 More actions · Answer 2

I wrote the following and it works for the case that I was talking about but it screws up my good ones, ex

14 -16 HEYWARD STREET

485 -BELLMORE AVENUE

Thanks for anyones time on this

CASE

WHEN CHARINDEX(' ', LTRIM(RTRIM(ADDRESS1)))

<> 0 AND CHARINDEX(' ', LTRIM(RTRIM(ADDRESS1)))

< PATINDEX('%[A-Z]%',LTRIM(RTRIM(ADDRESS1)))

--IF THE TEXT AFTER SPACE IS SAME

THEN (SUBSTRING(LTRIM(RTRIM(ADDRESS1)),1,CHARINDEX(' ', LTRIM(RTRIM(ADDRESS1)))))+''+

REPLACE(ADDRESS1,(SUBSTRING(LTRIM(RTRIM(ADDRESS1)),1,CHARINDEX(' ', LTRIM(RTRIM(ADDRESS1))))),'-')

END

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Can you try this.

update #t set address = replace(address, substring(address, charindex(' ', address) - 1, 1) + ' ' + substring(address, charindex(' ', address) + 1, 1), substring(address, charindex(' ', address) - 1, 1) + '-' + substring(address, charindex(' ', address) + 1, 1))

where isnumeric(substring(address, charindex(' ', address) - 1, 1)) = 1

and isnumeric(substring(address, charindex(' ', address) + 1, 1)) = 1

psconsulting SSC Eights! Points: 944 More actions · Answer 4

Allen_Cui,

With that I than have results like the following:

270-05-76TH AVENUE

133-38-41ST RD #C01

and it should be

270-05 76TH AVENUE

133-38 41ST RD #C01

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Can you post your original data? If the original data looks like following, You will get exactly what you want.

270 05 76TH AVENUE

133 38 41ST RD #C01

After running the update, The data looks as

270-05 76TH AVENUE

133-38 41ST RD #C01

psconsulting SSC Eights! Points: 944 More actions · Answer 6

Allen_Cui, You are def correct. It works with the data that I provided. But here is the original and output data using the statement you sent me that I still can't figure out what to do in.

Before query: 8782 20TH AVENUE

AFTER query: 8782-20TH AVENUE

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 7

Try this.

update #t set address = replace(address, substring(address, charindex(' ', address) - 1, 1) + ' ' + substring(address, charindex(' ', address) + 1, 1), substring(address, charindex(' ', address) - 1, 1) + '-' + substring(address, charindex(' ', address) + 1, 1))

where isnumeric(substring(address, charindex(' ', address) - 1, 1)) = 1

and isnumeric(substring(address, charindex(' ', address) + 1, 1)) = 1

and isnumeric(substring(substring(address, charindex(' ', address) + 1, len(address) - charindex(' ', address)), 1, charindex(' ', substring(address, charindex(' ', address) + 1, len(address) - charindex(' ', address))) - 1)) = 1

Before update, The data looks like.

270 05 76TH AVENUE

133 38 41ST RD #C01

8782 20TH AVENUE

1234 45 second ave

And after updated. It looks like as.

270-05 76TH AVENUE

133-38 41ST RD #C01

8782 20TH AVENUE

1234-45 second ave