Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Question Unanswered: Extract/Split values from a string...

    My table have an Address field and I must separate its street number and the street name.

    Something like this...

    123 main st
    1b second st
    145 third st
    12-45 fourth st

    Must split them to look like this


    |123| |main st|
    |1b| |second st|
    |145| |third st|
    |12-45| |fourth st|

    Since I'm still new to MSSQL queries and Stored Procedures. I don't have a clear idea on how to do this.

    Can someone help me?
    Last edited by BlueGemini; 10-17-08 at 11:20.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - that's changed a lot since your edit. Glad I didn't try answering your first question. This is correct now huh?

    The easy answer involves finding the very first space in the string. Are there any examples of rows that would have a space in between the first two pipes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How would you parse:

    674 ½ 53rd Street

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Me? That's why I asked the question - my fingers and twinkles are crossed hoping I won't have to
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by pootle flump
    Well - that's changed a lot since your edit. Glad I didn't try answering your first question. This is correct now huh?

    The easy answer involves finding the very first space in the string. Are there any examples of rows that would have a space in between the first two pipes?
    Actually, I also thought of that to use the first space as my delimiter... but I don't know how to begin with. I'm thinking of using SUBSTRING but I think that won't work.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BlueGemini
    I'm thinking of using SUBSTRING but I think that won't work.
    oh rly?

    air codified:
    Code:
    SELECT '|' + SUBSTRING(street, 1, CHARINDEX(' ', street)) + '| |' + SUBSTRING(street, CHARINDEX(' ', street)) + 1, LEN(street)) + '|' 
    FROM myTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by pootle flump
    oh rly?
    air codified:
    Code:
    SELECT '|' + SUBSTRING(street, 1, CHARINDEX(' ', street)) + '| |' + SUBSTRING(street, CHARINDEX(' ', street)) + 1, LEN(street)) + '|' 
    FROM myTable

    Hehehe! Like I said, I'm not yet familiar much in MSSQL so I just basically know some of it.

    I'll review this. It will be a good reference for me.

    Thank you very much!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My pleasure

    Remember - if you have any data that does not match the pattern you presented above, or may potentially get some in future, then things get more complicated
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by pootle flump
    My pleasure

    Remember - if you have any data that does not match the pattern you presented above, or may potentially get some in future, then things get more complicated
    Oh yeah, I just forgot... some addresses doesn't have street number, so the field for the street number will be just null and only the field of the street name will be filled.

    I made changes in your given code (removing those '|') and gave them designated fields. Well when running just your code, seems that conditions also applies (no street number just street name is ok) but when I'm applying it to my own, the street name is filled in the field of street number. A bit confuse.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Woah - please can you post the DDL of your table?

    Please refer here if you don't know how to:
    http://www.dbforums.com/showthread.php?t=1212452
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by pootle flump
    Woah - please can you post the DDL of your table?

    Please refer here if you don't know how to:
    http://www.dbforums.com/showthread.php?t=1212452
    This is the structure of my table

    Code:
    CREATE TABLE #RAW1
            (
    	[ID] INT IDENTITY (1,1),
    	BUSINESSNAME varchar (100),
    	ADDRESS varchar (100),
    	CITY varchar (100),
    	PROVINCE varchar (100),
    	PHONE varchar (100),
    	)
    I need to split the 'Street Number' and the 'Street Name' from the 'Address' Field and give them their own fields. So I apply the example you gave to me.


    Code:
    SELECT 
    	[ID],
    	BUSINESSNAME,
    	ADDRESS,
    	CITY,
    	PROVINCE,
    	PHONE,
           SUBSTRING(ADDRESS, 1, CHARINDEX(' ', ADDRESS)) AS STREETNO,
           SUBSTRING(ADDRESS, CHARINDEX(' ', ADDRESS), LEN(ADDRESS)) AS STREETNAME
    
    INTO #ANOTHER_RAW
    
    FROM #RAW1
    Your example is fine, actually and it able to split the values from the Address field but I forgot that their are instances that the Address doesn't have a street number. If that's the case the Streetno field would just leave it blank or null. With your given examples the values that suppose to be in the Streetname field was put in the Streetno.

    Lately, I'm trying to solve it using PATINDEX and CHARINDEX but I'm still working on it.
    Last edited by BlueGemini; 10-18-08 at 15:49.

  12. #12
    Join Date
    Jun 2008
    Posts
    49
    Ah, ok didn't saw that link first... sorry, my bad....

    So this is the table

    Code:
    CREATE TABLE #RAW1
            (
    	[ID] INT IDENTITY (1,1),
    	BUSINESSNAME varchar (100),
    	ADDRESS varchar (100),
    	CITY varchar (100),
    	PROVINCE varchar (100),
    	PHONE varchar (100),
    	)
    Some of the listings for examples...

    Code:
    INSERT INTO #RAW1(BUSINESSNAME, ADDRESS, CITY, PROVINCE, PHONE)
    SELECT 'SAMPLE BUSINESS NAME1', '123 MAIN ST','CITY1','PROVINCE1','555-123456' 
    UNION ALL
    SELECT 'SAMPLE BUSINESS NAME2', '10-B SECOND ST' ,'CITY2','PROVINCE2','046-1112222' 
    UNION ALL
    SELECT 'SAMPLE BUSINESS NAME3', 'THIRD ST' ,'CITY3','PROVINCE3','777-8888' 
    UNION ALL
    SELECT 'SAMPLE BUSINESS NAME4', '111-222 ANOTHER ST' ,'CITY4','PROVINCE4','888-7777'
    Like I said -- I need to split the 'Street Number' and the 'Street Name' from the 'Address' Field and give them their own fields. So I apply the example you gave to me.

    Code:
    SELECT 
    	[ID],
    	BUSINESSNAME,
    	ADDRESS,
    	CITY,
    	PROVINCE,
    	PHONE,
           SUBSTRING(ADDRESS, 1, CHARINDEX(' ', ADDRESS)) AS STREETNO,
           SUBSTRING(ADDRESS, CHARINDEX(' ', ADDRESS), LEN(ADDRESS)) AS STREETNAME
    
    FROM #RAW1
    Your example is fine, actually and it able to split the values from the Address field but I forgot that their are instances that the Address doesn't have a street number. If that's the case the Streetno field would just leave it blank or null. With your given examples the values that suppose to be in the Streetname field was put in the Streetno. I'll be just showing the results in the STREETNO and STREETNAME


    Code:
    ID       STREETNO                 STREETNAME
    ---      -------------            -----------------
    1         123                          MAIN ST
    2         10-B                        SECOND ST
    3         THIRD                      ST
    4         111-222                   ANOTHER ST
    My problem is the 3rd record

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for that. I did get the wrong end of the stick there.
    Code:
    SELECT 
        [ID],
        BUSINESSNAME,
        ADDRESS,
        CITY,
        PROVINCE,
        PHONE,
            STREETNO    = CASE 
                    WHEN ADRESS LIKE '[0-9%]' THEN
                        SUBSTRING(ADDRESS, 1, CHARINDEX(' ', ADDRESS))
                  END,
            STREETNAME    = CASE 
                    WHEN ADRESS LIKE '[0-9%]' THEN
                              SUBSTRING(ADDRESS, CHARINDEX(' ', ADDRESS), LEN(ADDRESS))
                    ELSE
                        ADDRESS
                         END 
    FROM #RAW1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by pootle flump
    Thanks for that. I did get the wrong end of the stick there.
    Code:
    SELECT 
        [ID],
        BUSINESSNAME,
        ADDRESS,
        CITY,
        PROVINCE,
        PHONE,
            STREETNO    = CASE 
                    WHEN ADRESS LIKE '[0-9%]' THEN
                        SUBSTRING(ADDRESS, 1, CHARINDEX(' ', ADDRESS))
                  END,
            STREETNAME    = CASE 
                    WHEN ADRESS LIKE '[0-9%]' THEN
                              SUBSTRING(ADDRESS, CHARINDEX(' ', ADDRESS), LEN(ADDRESS))
                    ELSE
                        ADDRESS
                         END 
    FROM #RAW1
    Sorry also. Your first given code was actually helpful already to me. I just forgot some cases about the ADDRESS. I'll check this later but if ever their would be problems again, I think I can manage now myself. Thanks for those references you gave me. Its really helpful for me to learn queries of MSSQL

    Thank you very much!

  15. #15
    Join Date
    Jun 2008
    Posts
    49
    Hey, I already tested the code. There's one wrong here...

    The expression '[0-9%]' should be this '[0-9]%' . Maybe its just a typo so its ok

    Anyway, the code is working

    Thank very much again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •