Inserting space after number and before letters

• rash3554

  SSCommitted

  Points: 1984

  More actions

  June 10, 2016 at 4:20 am

  #314486

  Hi there,

  I have a table with home address with missing spaces between House number and streetname. How can I insert spaces?

  Create table dbo.streetaddr(Address varchar(200));

  INSERT INTO dbo.streetaddr VALUES('656ave.');

  INSERT INTO dbo.streetaddr VALUES('B-6 ');

  INSERT INTO dbo.streetaddr VALUES('13villa ');

  INSERT INTO dbo.streetaddr VALUES('25Main street');

  I want

  656 ave.

  B-6

  13 villa

  25 Main street

  How can I do this?

  Thx

  Rs

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 10, 2016 at 5:01 am

  #1884341

  rash3554 (6/10/2016)

  ---

  Hi there,

  I have a table with home address with missing spaces between House number and streetname. How can I insert spaces?

  Create table dbo.streetaddr(Address varchar(200));

  INSERT INTO dbo.streetaddr VALUES('656ave.');

  INSERT INTO dbo.streetaddr VALUES('B-6 ');

  INSERT INTO dbo.streetaddr VALUES('13villa ');

  INSERT INTO dbo.streetaddr VALUES('25Main street');

  I want

  656 ave.

  B-6

  13 villa

  25 Main street

  How can I do this?

  Thx

  Rs

  SELECT *, NewAddress = STUFF([Address],x.pos+1,0,' ')

  FROM streetaddr

  CROSS APPLY (SELECT pos = PATINDEX('%[0-9][a-z]%',[Address])) x

  WHERE x.pos > 0

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden