Re order string in field - 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 2005 Forums

Transact-SQL (2005)

Re order string in field

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2010-11-02 : 05:52:06

Hi All,

I got a field with part of address, the street and nr.
ex: james street 53

But french put nr and street the other way around

ex: 53 James street

Now i need to get it to English format to import into a 3rd party program

How do i do that?

Thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-02 : 06:05:52

select address, substring(address,charindex(' ',address)+1,len(address))+left(address,charindex(' ',address)-1) from your_table

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2010-11-02 : 06:21:21

Thanks for the answer.
But i forgot to mention 1 point:
I need to detect if the huis nr is in the beginning before doing the conversions.
Since they are mixed.

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 06:51:06

Who decided to allow all parts of a street address to be stored in a single column?
If it's no to late, redesign and use two columns; "Street Name" and "Street number".
You can also use "Apartment number" and "Floor" as additional address information.

N 56°04'39.26"
E 12°55'05.63"

mike13
Posting Yak Master

219 Posts

Posted - 2010-11-02 : 06:56:32

Hi,

That is too late.
Talking about 200K address here!

There must be a what to detect if it is numeric?

thanks all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-02 : 07:05:29

quote:
Originally posted by mike13

Thanks for the answer.
But i forgot to mention 1 point:
I need to detect if the huis nr is in the beginning before doing the conversions.
Since they are mixed.

Thanks a lot

select address, substring(address,charindex(' ',address)+1,len(address))+left(address,charindex(' ',address)-1) from your_table
where left(address,charindex(' ',address)-1) not like '[^0-9]

Madhivanan

Failing to plan is Planning to fail

mike13
Posting Yak Master

219 Posts

Posted - 2010-11-02 : 07:17:51

Hi,

The french address are working fine.
But it is missing up the other address like this german address:
Original:
Landsbergerstr 23

After query:
23Landsbergerstr

here is my SP

ALTER PROCEDURE SP_Back_Order_GetCSV
AS
SELECT keycode, REPLACE(Name1, ',', '-') AS Name1,
substring( REPLACE(address1, ',', '-'),charindex(' ', REPLACE(address1, ',', '-'))+1,len( REPLACE(address1, ',', '-')))+left( REPLACE(address1, ',', '-'),charindex(' ', REPLACE(address1, ',', '-'))-1)

AS address1,
REPLACE(address3, ',', '-') AS city, REPLACE(Postalcode, ',', '') AS Postalcode,country, Countrycode, Weight, value, phone, site
FROM V_Back_Order_CSV
WHERE (type = 0) and left(address1,charindex(' ',address1)-1) not like '[^0-9]'

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources