By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

I want to separate the column data into two columns

P: 22
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.
Attached Images
File Type: png Figure 1.png (8.4 KB, 83 views)
File Type: png Figure 2.png (8.8 KB, 93 views)
Sep 3 '14 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,430
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.
Sep 3 '14 #2

P: 22
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.
Sep 4 '14 #3

jforbes
Expert 100+
P: 1,107
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function getAddressWithoutPin(ByRef sAddress As String) As String
  2.     getAddressWithoutPin = Left(sAddress, InStrRev(sAddress, " ") - 1)
  3. End Function
  4.  
  5. Public Function getAddressPin(ByRef sAddress As String) As String
  6.     getAddressPin = Right(sAddress, Len(sAddress) - InStrRev(sAddress, " "))
  7. End Function
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT getAddressWithoutPin([Address]) AS JustAddress, getAddressPin([Address]) AS Pin FROM YourTable;
Sep 4 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.