Splitting data into two columns

• Senchi

  Ten Centuries

  Points: 1184

  More actions

  February 24, 2017 at 9:24 pm

  #335263

  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.
  

• Thom A

  SSC Guru

  Points: 99137

  More actions

  February 25, 2017 at 5:04 am

  #1930312

  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

• Senchi

  Ten Centuries

  Points: 1184

  More actions

  February 25, 2017 at 7:06 am

  #1930321

  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'.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  February 25, 2017 at 7:47 am

  #1930323

  Senchi - Saturday, February 25, 2017 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'.

  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

• Senchi

  Ten Centuries

  Points: 1184

  More actions

  February 25, 2017 at 7:54 am

  #1930324

  J Livingston SQL - Saturday, February 25, 2017 7:47 AM

  Senchi - Saturday, February 25, 2017 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'.

  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.:)