February 24, 2017 at 9:24 pm
In address_field I have street name together with house number all in one line.
Something like : One Street 1A
I need this separated into two columns.
I want the house number to go into the house_number column
I need a query that will catch the first number in the address field and all data after it and move it
to the house_number column.
So I have :
Address House_Number
One Street 1A
Here the table :USE [tempdb]
GO
CREATE TABLE [dbo].[TEMP]
(
[adress] [nvarchar](50),
[street_number] [nvarchar](5)
)
go
INSERT INTO TEMP (adress)
values
('One Street 1A'),
('Two Street 2AB'),
('Three Street 3BA')
How can I do this ?
Ohh,and I forgot to ask also: Is it possible to split the street number into numbers and letters so they are not together?
Like if it's 1A then it becomes 1 A.
February 25, 2017 at 5:04 am
This splits out the two parts, but not the letters and numbers:CREATE TABLE #Sample ([address] nvarchar(50), --Address has 2 Ds, not 1
street_number nvarchar(5));
GO
INSERT INTO #Sample ([address])
VALUES
('One Street 1A'),
('Two Street 2AB'),
('Three Street 3BA');
GO
SELECT *
FROM #Sample;
WITH AddressSplit AS (
SELECT [address],
RIGHT([address], CHARINDEX( ' ', REVERSE(Address)) - 1) AS Number
FROM #Sample)
SELECT LEFT([address], LEN([address]) - len(Number) - 1) AS Street,
Number
FROM AddressSplit;
GO
DROP TABLE #Sample;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 25, 2017 at 7:06 am
Wow, Thom....wonderful ..
What if I have the case when numbers and letters are separated like : 1 A ?
Right now, when I tested , soon as I add space it does not work. 1A inserts OK, 1 A does not.
Only 'A' goes into 'number'.
February 25, 2017 at 7:47 am
Senchi - Saturday, February 25, 2017 7:06 AMWow, Thom....wonderful ..
What if I have the case when numbers and letters are separated like : 1 A ?
Right now, when I tested , soon as I add space it does not work. 1A inserts OK, 1 A does not.
Only 'A' goes into 'number'.
probably best for everyone if you provide all the possible variants you may have...dont you think?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 25, 2017 at 7:54 am
J Livingston SQL - Saturday, February 25, 2017 7:47 AMSenchi - Saturday, February 25, 2017 7:06 AMWow, Thom....wonderful ..
What if I have the case when numbers and letters are separated like : 1 A ?
Right now, when I tested , soon as I add space it does not work. 1A inserts OK, 1 A does not.
Only 'A' goes into 'number'.probably best for everyone if you provide all the possible variants you may have...dont you think?
well, usually its 20B or 20 B . I cant think of anything else sane.:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply