CODEWITHSUNDEEP
sqlsql-server
Habtamu Abera
Habtamu Abera

Reputation: 19

Splitting a column into two

I just want to split an address column containing both street number and street name into two columns:Street number and Street name. The street number and name are separated by space in the address column. I used the following script to split the address I give into 2 columns based on the space delimiter.

select left(Address, CHARINDEX(' ', Address)) as Street_Number,
       substring(Address, CHARINDEX(' ', Address)+1, len(Address)-(CHARINDEX(' ', Address)-1)) as Street_Name
from [dbo].XYZ

The issue is in some cases I have addresses only with street name and in this case I want the street number column left blank\null.

Upvotes: 1

Views: 96

Answers (2)

Habtamu Abera
Habtamu Abera

Reputation: 19

This is how I addressed it. I hope this will help.

Create Database TestDB go

USE TestDB GO --Create Sample Table CustomerAddress create table CustomerAddress(Address char(100)) go

insert into CustomerAddress values('123 Main St') insert into CustomerAddress values('XYZ St') insert into CustomerAddress values(' abc')

select * from CustomerAddress

--Option #1a - Split Address column, when no street number replace with empty value SELECT
Street_Number = CASE WHEN (ISNUMERIC(LEFT(Address, 1)) = 1) THEN LEFT(Address, CHARINDEX(' ', Address)) ELSE '' END , Street_Name = CASE WHEN (ISNUMERIC(LEFT(Address, 1)) = 1) THEN substring(Address, CHARINDEX(' ', Address) + 1, len(Address) - (CHARINDEX(' ', Address) - 1)) ELSE Address END FROM [dbo].CustomerAddress;

--Option #1b - Split Address column, when no street number replace with NULL SELECT
Street_Number = CASE WHEN (ISNUMERIC(LEFT(Address, 1)) = 1) THEN LEFT(Address, CHARINDEX(' ', Address)) ELSE NULL END , Street_Name = CASE WHEN (ISNUMERIC(LEFT(Address, 1)) = 1) THEN substring(Address, CHARINDEX(' ', Address) + 1, len(Address) - (CHARINDEX(' ', Address) - 1)) ELSE Address END FROM [dbo].CustomerAddress;

--Option #2a - Use LIKE % instead of ISNUMERIC, we may get better performance

SELECT
Street_Number = CASE WHEN (Address LIKE '[0-9]%') THEN LEFT(Address, CHARINDEX(' ', Address)) ELSE NULL END ,

Street_Name = CASE WHEN (Address LIKE '[0-9]%') THEN substring(Address, CHARINDEX(' ', Address) + 1, len(Address) - (CHARINDEX(' ', Address) - 1)) ELSE Address END FROM [dbo].CustomerAddress;

--Clean up by dropping the table drop table [dbo].CustomerAddress go

Upvotes: 0

Alex K.
Alex K.

Reputation: 175956

One way:

;with t(Address) as (
    select '123 street lane' union all
    select '12a lane' union all
    select 'street lane' union all
    select 'street'
)
select 
    Address,
    case when Address like '[0-9]%' then
        left(Address, charindex(' ', Address) - 1)
    else
        ''
    end as Street_Number,
    case when Address like '[0-9]%' then
        substring(Address, charindex(' ', Address) + 1, len(Address))
    else
        Address
    end as Street_Name
from t

>>

Address         Street_Number   Street_Name
--------------- --------------- ---------------
123 street lane 123             street lane
12a lane        12a             lane
street lane                     street lane
street                          street

Upvotes: 2

Related Questions