CODEWITHSUNDEEP
sql-serversplit
Aju
Aju

Reputation: 1

Sql Server Split up the column

I have a table contact_add. I have one column City which contains (MANGALORE-575001) I want a query to split up the City and Pincode in SQL server. If Substring Expression, start and length have to be given, which may differ for each city

EXAMPLE:

Mangalore-123456 
Kannur-6542 
kochi-78954 
Goa-12

I want a query to split up the name and pincode into two columns like below 

city      Pincode 
--------- ------- 
Mangalore 123456 
Kannur    6542 
Kochi     78954 
Goa       12

Upvotes: 0

Views: 91

Answers (2)

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

Try this...

select substring('MANGALORE-575001',0,CHARINDEX('-','MANGALORE-575001')), substring('MANGALORE-575001',CHARINDEX('-','MANGALORE-575001')+1,len('MANGALORE-575001'))

Upvotes: 0

User Learning
User Learning

Reputation: 3473

TRY this:

SELECT 
    CASE WHEN CHARINDEX('-',city)>0 THEN SUBSTRING(city,1,CHARINDEX('-',city)-1) 
         ELSE city end City, 
    CASE WHEN CHARINDEX('-',city)>0  THEN SUBSTRING(city,CHARINDEX('-',city)+1,len(city))  
         ELSE NULL END as pincode
FROM Contact_add

city = column name at line2

Upvotes: 2

Related Questions