locked
Split data into two columns based on where space appears RRS feed

  • 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

    Wednesday, July 8, 2015 6:38 PM

Answers

  • 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. :)
    • Marked as answer by wha59 Wednesday, July 8, 2015 7:51 PM
    Wednesday, July 8, 2015 6:49 PM
  • 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

    • Marked as answer by Eric__Zhang Thursday, July 16, 2015 1:02 AM
    Wednesday, July 8, 2015 7:51 PM

All replies

  • 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

    Wednesday, July 8, 2015 6:47 PM
  • 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. :)
    • Marked as answer by wha59 Wednesday, July 8, 2015 7:51 PM
    Wednesday, July 8, 2015 6:49 PM
  • 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

    • Marked as answer by Eric__Zhang Thursday, July 16, 2015 1:02 AM
    Wednesday, July 8, 2015 7:51 PM
  • 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.


    Wednesday, July 8, 2015 8:00 PM
  • Hi Patrick

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

    John

    Thursday, July 9, 2015 12:58 PM