Hello

I have a problem, I was given a table full of addresses and I found that
zipcode and city were in same column, so I need to run an update that
separates those.

Problem is that there are 2 spaces so I cant just split at a space.

Format: 123 45 City

How I do that?

/Lasse

and i noticed that some are with only 1 space :(

as in Format: 12345 City


Show quoteHide quote

Try this:

declare    @str1    varchar(256)
declare    @str2    varchar(256)

set    @str1 = '123 45 City'
set    @str2 = '12345 City'

select    replace(substring(@str1, 1, patindex('%[^0-9 ]%', @str1) - 1), ' ', '')
    ,replace(substring(@str2, 1, patindex('%[^0-9 ]%', @str2) - 1), ' ', '')


ML

---
http://milambda.blogspot.com/

Ok, both values this time:

declare    @str1    varchar(256)
declare    @str2    varchar(256)

set    @str1 = '123 45 City'
set    @str2 = '12345 City'

select    replace(substring(@str1, 1, patindex('%[^0-9 ]%', @str1) - 1), ' ', '')
    ,substring(@str1, patindex('%[^0-9 ]%', @str1), datalength(@str1))

select    replace(substring(@str2, 1, patindex('%[^0-9 ]%', @str2) - 1), ' ', '')
    ,substring(@str2, patindex('%[^0-9 ]%', @str2), datalength(@str2))



ML

---
http://milambda.blogspot.com/

ML,

thx how would it work if it was a [anychars + space + anychars] ? :)

i.e abc a4sadf, 23d3e 4342d




Show quoteHide quote

That would require a slightly different approach - please post more sample
data.

However, to tackle this one the proper SQL-way (using a set-based approach)
you'd need a list of all valid post codes. Can you get a valid list?

A set-based solution usually outperforms any solution based on procedural
logic. It's also less difficult to understand and maintain.


ML

---
http://milambda.blogspot.com/

For the regulars on this ng the matter is SET-tled.For the rest of the
industry it's precisely the opposite. Forty years of history and MSs own
future direction substantiate such a conclusion.For many sql is a
crutch for the lost ability to see a solution any other way.

http://racster.blogspot.com

Excellent Steve. If you are going to keep entertaining us, then boldly plug
your product!

Show quoteHide quote

Au Contraire.. could it be you have error'd in your interpretation?
All dots do not lead back to Rac.After all I didn't give the rac link.
Of course 'entertainment' is highly subjective.So I will accept a
50% solution:( :)

'http://racster.blogspot.com'

Yes, great products sell themselves.


ML

---
http://milambda.blogspot.com/

Bing, Bam,Woush,Ouch,Ouch,Ouch.
1,2,3.....Your Out!

Good Zinger! even if based on the wrong path :-)

You seem to be a good sport.We can get along:)

best,
steve

Here is another way

declare @str1   varchar(256)
declare @str2   varchar(256)
declare @str3   varchar(256)


set     @str1 = '123 45 City'
set     @str2 = '12345 City'
set     @str3 = '12345        City'


select  ltrim(right(@str1,patindex('% %', reverse(@str1)))) as City,
        left(@str1,(len(@str1) -(patindex('% %', @str1))))


select  ltrim(right(@str2,patindex('% %', reverse(@str2)))) as City,
        left(@str2,(len(@str2) -(patindex('% %', @str2))))

select  ltrim(right(@str3,patindex('% %', reverse(@str3)))) as City,
        left(@str3,(len(@str3) -(patindex('% %', @str3))))

Denis the SQL Menace
http://sqlservercode.blogspot.com/

ML wrote:
Show quoteHide quote

You can nest REPLACE() functions about 32 levels deep

UPDATE Addresses
--remove all digits
SET city =
REPLACE (
REPLACE (
  ..
  REPLACE (city_zip, '9', ''),
  '8', ''),
..
'0', '')
--remove all alphas and spaces
zip =
REPLACE (
REPLACE (
  ..
  REPLACE (LOWER(city_zip), 'z', ''),
  'y', ''),
..
'a', '')
' ', '');

Then make another update to clean up leading and double spacing in the
city column.

I doubt if REPLACE() has a 32 level nesting restriction.

With SQL Server, REPLACE nesting levels can go way upto more than 500
levels, depending on the number of characters in its first argument.

Here are 44 of them

declare @v varchar(500)
select @v ='qwertyuiopasdfghjklzxcvbnm1234567890)(*&^%$#@!'

select replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
replace(replace(@v,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8','')
,'9',''),'0',''),'A',''),'B',''),'C',''),'D','')
,'E',''),'F',''),'G',''),'H',''),'I',''),'J','')
,'K',''),'L',''),'M',''),'N',''),'O',''),'P','')
,'Q',''),'R',''),'S',''),'T',''),'U',''),'V','')
,'W',''),'X',''),'Y',''),'Z',''),'(',''),')','')
,'*',''),'&',''),'^',''),'%',''),'$',''),'#','')


Denis the SQL Menace
http://sqlservercode.blogspot.com/


Anith Sen wrote:
Show quoteHide quote

Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
error converting datatype varchar to numeric
Update Query in SQL 2005 with inner join
nesting select statements