String manipulation

[22strider]

Hello Friends

Could you please help me with the following?

In one of my tables I have column Address. Typical address looks like 6158 1313Street Suit12
I need it to appear like Suit12 6158 1313Street.
Not all the addresses have Suit in them.

Thanks for your help
Rajesh

 

[alansidman]

Here's a video on data manipulation that you should find helpful and give you some ideas on how to accomplish your end result.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

Alan

 

[22strider]

very informative video; but what formula should I use to update (for bringing SUITE infront of the addresses)?

 

[alansidman]

Using the same concepts, I used the Len Function, the Right Function, the Left Function and the Instr Function. The Instr Function acts much like the find function in Excel. Here is the SQL Statement

SELECT Table1.ID, Table1.Address, Len([Address]) AS Length, InStr([Address],"Suite") AS Suite,
Right([Address],Len([Address])-InStr([Address],"Suite")+1) AS Suite1,
Left([Address],Len([Address])-Len([Suite1])) AS Address1, 
[Suite1] & " " & [Address1] AS TAddress
FROM Table1;

You will have to change your names accordingly. There are some superfluous fields here but once you figure this on your own, you can delete them.

Alan

 

[22strider]

thankyou very much