Split data into two columns based on where space appears

Question

Hi

I have a field in a table that contains addresses e.g

15 Green Street

5F Brown Steet

127 Blue Street

1512 Red Road

I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.

So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.

Can anyone help

Thanks in advance

John

Answer 1

Check it out:

https://msdn.microsoft.com/en-us/library/ms186323.aspx?f=255&MSPPError=-2147217396

---

If there was a problem - Yo, I'll solve it

Answer 2

Hi

I have a field in a table that contains addresses e.g

15 Green Street

5F Brown Steet

127 Blue Street

1512 Red Road

I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.

So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.

Can anyone help

Thanks in advance

John

DECLARE @DEMO TABLE (STREET varchar(50))
INSERT INTO @DEMO (STREET) VALUES ('15 Green Street'), ('5F Brown Steet'), ('127 Blue Street'), ('1512 Red Road')

SELECT STREET, 
	SUBSTRING(STREET, 0, CHARINDEX(' ', STREET, 0)) as Number,
	SUBSTRING(STREET, 1 + CHARINDEX(' ', STREET, 0), LEN(STREET) - CHARINDEX(' ', STREET, 0)) as Street
FROM @DEMO

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

Answer 3

Hi Daniel

Many thanks for you assistance you pointed me in the right direction

Your code returned the following error:

Msg 537, Level 16, State 2, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

I amended the code to

SELECT add_2,
    SUBSTRING(add_2, 0, CHARINDEX(' ', add_2, 0)) as Number,
    SUBSTRING(add_2,CHARINDEX(' ',add_2)+1,LEN(add_2))
FROM
   cmpadd

and this worked as required

Answer 4

As your example data demonstrates, it's only going to split them one way. (line 6 has characters n the number field, and numbers in the street field).

Try a TVF, like this:

CREATE FUNCTION [dbo].[alphaNumericSeperatorX](@stringIn VARCHAR(MAX))
RETURNS @result TABLE (letters VARCHAR(MAX), numbers VARCHAR(MAX), versionCode VARCHAR(MAX))
AS
BEGIN

DECLARE @letters VARCHAR(MAX), @numbers VARCHAR(MAX), @versionCode VARCHAR(MAX)
SET @numbers = ''
SET @letters = ''

WHILE LEN(@stringIn) > 0
BEGIN
 IF LEFT(@stringIn,1) IN ('0','1','2','3','4','5','6','7','8','9') SET @numbers = @numbers + LEFT(@stringIn,1)
 IF LEFT(@stringIn,1) NOT IN ('0','1','2','3','4','5','6','7','8','9') SET @letters = @letters + LEFT(@stringIn,1)
 SET @stringIn = RIGHT(@stringIn,LEN(@stringIn)-1)
END
INSERT INTO @result VALUES (@letters,@numbers,@versionCode)
RETURN
END

SELECT *
  FROM [dbo].[alphaNumericSeperatorx]('xxx 123 any street')

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Answer 5

Hi Patrick

Thank you for reply i will have a look at this option

John