I want to separate the column data into two columns

[phanikumar32]

Hi All,

I have a table in sql server with some tables.
And in that,one of the column on one table has the data like shown in figure 1.

Now i want to separate(or)split in to two columns like shown in the figure 2.

so could any one please provide the query for getting my required output.

Thanks In Advance,
Phani Kumar.

[Rabbit]

Use the RIGHT() function to get the last 10 characters and the LEFT() function with the LEN() function to get the left x characters where x is equal to th length returned from LEN() minus 11.

[phanikumar32]

Thank you Rabbit for your information.
what you are suggested me is that fine only the pincode has 10 characters(from Right to Left).

so if any of the address has lessthan or greaterthan the 10 characters how i will separate.

Please suggest me and Please provide me the query for both types(If the pincode characters are in static state means 10 characters and the second type is,the pincode characters are in dynamic state means lessthan or greaterthan the 10 characters)


Thanks In Advance,
Phani Kumar.

[jforbes]

Hello Phani Kumar,

Building on what Rabbit has supplied you with, you could build a couple functions like this to separate the Address by its Last Space:

Public Function getAddressWithoutPin(ByRef sAddress As String) As String
    getAddressWithoutPin = Left(sAddress, InStrRev(sAddress, " ") - 1)
End Function
 
Public Function getAddressPin(ByRef sAddress As String) As String
    getAddressPin = Right(sAddress, Len(sAddress) - InStrRev(sAddress, " "))
End Function
 

Then if I understand your post correctly, you need to see the results in a select. With the given information, this is the closest I could manage:

SELECT getAddressWithoutPin([Address]) AS JustAddress, getAddressPin([Address]) AS Pin FROM YourTable;