extract columns from a string column
-
I would like to extract a street address to separate columns.
For example, I have street fake data only these two patterns, I want to separate them to different columns.
all the street name has one word house number, one word streetname, one word street type, and some of them has an apartment number, that has a comma as separator in front of it.
What function should I use to separate them?
Column will be houseNum, StreetName, streetType, and some of them has an apt number : sample data
93583 Dogwood Way
67121 Fourteenth Dr
72905 Cedar Cir
81774 South St, 14412
Script to create tables
CREATE TABLE [dbo].[testAddress](
[ID] [int] NULL,
[houseNum] [int] NULL,
[streetNm] [varchar](50) NULL,
[StreetType] [varchar](5) NULL,
[aptno] [varchar](10) NULL,
[fullstreet] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[testAddress] ([ID], [houseNum], [streetNm], [StreetType], [aptno], [fullstreet]) VALUES (1, 93583, N'Dogwood', N'Way', NULL, N'93583 Dogwood Way')
GO
INSERT [dbo].[testAddress] ([ID], [houseNum], [streetNm], [StreetType], [aptno], [fullstreet]) VALUES (2, 81774, N'South St', N'ST', N'4412', N'81774 South St, 14412')
GO
-
Thanks for the interesting problem... Perhaps consider something like this:
SELECT ID, b.[value] bVal , .ordinal bOrd, CASE WHEN CHARINDEX ( ',', fullStreet ) = 0 THEN ''
ELSE SUBSTRING(fullStreet, CHARINDEX ( ',', fullStreet ) +2, LEN(fullStreet)
) end as aptNO
FROM #t
CROSS APPLY STRING_SPLIT(SUBSTRING(fullStreet, 1, case when CHARINDEX ( ',', fullStreet ) = 0 THEN Len(fullStreet) ELSE CHARINDEX ( ',', fullStreet ) -1
END ), ' ', 1) bIt will give you a result set of:
You could then use the bValue and bOrd columns to assemble data into "[houseNum], [streetNm], [StreetType]," (bOrd = 1 is houseNum, bOrd =2 is streetNm, bOrd = 3 is streetType. Maybe pivot operator or max(bValue) where bord = 1 GROUP By ID.
I'll spend some time on the pivot query for that result set later today, just don't have the time right now, I wanted to get a possible solution to you.
I'm looking forward to other suggestions.
-
Try this:
drop table if exists #testAddress
CREATE TABLE #testAddress(
[ID] [int] NULL,
[houseNum] [int] NULL,
[streetNm] [varchar](50) NULL,
[StreetType] [varchar](5) NULL,
[aptno] [varchar](10) NULL,
[fullstreet] [varchar](100) NULL
)
INSERT #testAddress ([ID], [houseNum], [streetNm], [StreetType], [aptno], [fullstreet]) VALUES (1, 93583, N'Dogwood', N'Way', NULL, N'93583 Dogwood Way')
GO
INSERT #testAddress ([ID], [houseNum], [streetNm], [StreetType], [aptno], [fullstreet]) VALUES (2, 81774, N'South St', N'ST', N'4412', N'81774 South St, 14412')
Drop table if exists #tc
SELECT ID, b.[value] bVal , .ordinal bOrd, CASE WHEN CHARINDEX ( ',', fullStreet ) = 0 THEN ''
ELSE SUBSTRING(fullStreet, CHARINDEX ( ',', fullStreet ) +2, LEN(fullStreet)
) end as aptNo
INTO #tC
FROM #testAddress
CROSS APPLY STRING_SPLIT(SUBSTRING(fullStreet, 1, case when CHARINDEX ( ',', fullStreet ) = 0 THEN Len(fullStreet) ELSE CHARINDEX ( ',', fullStreet ) -1
END ), ' ', 1) b
-- Take a look at #tc:
SELECT * FROM #tc
-- pivot #tc for final results
SELECT id, [1] as [houseNum], [2] as [streetNm], [3] as [StreetType], [aptno]
FROM (
select id, [bOrd], [bVal], aptNo
from #tc
) a
PIVOT (
MAX([bVal])
FOR [bOrd] in (
[1], [2], [3]
)
) AS pt -
That works perfectly, thanks much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply