Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,

I have a four column for one address in my table and every each column size is 50 character length, so i want to split data after 50 character int second or third column.
like example:-
Address
140/3 abcd, narayana, delhi,pin code-201101

i want to split this address after 50 character.

How can be possible in sql server 2008?

Please help me.

Thanks
Ankit Agarwal
Software Engineer
Posted
Comments
AnkitGoel.com 14-May-13 2:44am    
why do you want to split?

My favorite method is to use Common Table Expressions[^].
Example:
SQL
DECLARE @fullAddress NVARCHAR(255)

SET @fullAddress  = '140/3 abcd, narayana, delhi,pin code-201101'

;With AddressParts AS
(
    SELECT 1 AS PartNumber, LTRIM(LEFT(@fullAddress, CHARINDEX(',',@fullAddress)-1)) AS Part, RIGHT(@fullAddress, LEN(@fullAddress) - CHARINDEX(',',@fullAddress)) AS Remainder
    UNION ALL
    SELECT PartNumber + 1 AS PartNumber, LTRIM(LEFT(Remainder, CHARINDEX(',',Remainder)-1)) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',',Remainder)) AS Remainder
    FROM AddressParts
    WHERE CHARINDEX(',', Remainder)>0
    UNION ALL
    SELECT PartNumber + 1 AS PartNumber, LTRIM(Remainder) AS Part, NULL AS Remainder
    FROM AddressParts
    WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM AddressParts


Above query returns:
PartNo. Part             Remainder
1	140/3 abcd	 narayana, delhi,pin code-201101
2	narayana	 delhi,pin code-201101
3	delhi	         pin code-201101
4	pin code-201101	 NULL
 
Share this answer
 
Comments
RedDk 14-May-13 12:21pm    
Awesome! C-T-E ... again.
Maciej Los 14-May-13 15:05pm    
Thank you, RedDK ;)
Maciej Los 15-May-13 1:51am    
Thank you for your opinion: "These article only waste my time". Now, i'm happy and i want help you more now... true?

I wan't help for touchy, abusive people!
[no name] 15-May-13 2:24am    
Sorry for that line "only waste my time".
How about

SQL
select left(FIELDNAME, 50) as AddressPart1 from TABLENAME
 
Share this answer
 
Comments
[no name] 14-May-13 2:43am    
@_Damian S_:5+
Maciej Los 14-May-13 4:03am    
Nice, but not complex. It returns only first part of address.
+4!
_Damian S_ 15-May-13 1:33am    
For that little outburst, there shall be no further help for you from me. The answer I gave you above showed you how to retrieve the first 50 chars... I won't be doing all your work for you, but pointing you in the right direction so that you can actually learn something. Don't you dare tell me I've wasted YOUR time...
Maciej Los 15-May-13 2:37am    
Damian, is this comment addressed to me? I didn't told you, that you "waste my time"...
_Damian S_ 15-May-13 2:46am    
No, it was addressed to the OP, who has since deleted his post, which makes it seem as though it is a reply to you.
 
Share this answer
 
v2
Comments
Maciej Los 14-May-13 4:02am    
Interesting links.
+5!
[no name] 14-May-13 4:03am    
@Maciej Los :Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900