Results 1 to 8 of 8

Thread: Substring

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Substring

    I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:

    Coulumn1
    -------------------------------
    SOUTH EL MONTE CA91733617
    BOSSIER LA71172
    GARDENA CA90249107
    MILWAUKEE WI53216
    PARIS IL61944
    DUQUOIN IL62832
    REDWOOD FALLS MN56283
    AUBURN ME04210


    I tryed this:

    use cimpro1
    select substring(cust_shipto_addr_l3, 1, 19) as 'City',
    substring(cust_shipto_addr_l3, 20, 21) as 'State',
    substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
    from opcshto


    For some reason, when I run the query I get this for State:

    State
    -------------------------
    CA91733617
    LA71172
    CA90249107
    WI53216
    IL61944
    IL62832
    MN56283
    ME04210


    When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.

    Any help is appreciated.
    Last edited by estefex; 06-02-04 at 11:46.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    3rd parameter should be the length.

    Instead of
    select substring(cust_shipto_addr_l3, 1, 19) as 'City',
    substring(cust_shipto_addr_l3, 20, 21) as 'State',
    substring (cust_shipto_addr_l3, 22, 31) as 'Zip'

    try
    select substring(cust_shipto_addr_l3, 1, 19) as 'City',
    substring(cust_shipto_addr_l3, 20, 2) as 'State',
    substring (cust_shipto_addr_l3, 22, 5) as 'Zip'

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Here is the BOL syntax for substring:

    SUBSTRING ( expression , start , length )

    so if you have substring(cust_shipto_addr_l3, 20, 21) you want the substring beginning at position 20 for a length of 21, not 2. If you only want 2 characters, you should only put a length of 2 characters, not 21. Books online (BOL) really does have some good information! Try it, you might like it!

  4. #4
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    you could also reverse the string so you could work from right to left in case City is going to be variable length

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    Thanks for all the help. I was getting the last parameter misxed up. That would actually do the trick.

    Regards

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by estefex
    Thanks for all the help. I was getting the last parameter misxed up. That would actually do the trick.

    Regards

    No it won't...your offsets will all be different

    You need a delimeter...space comes to mind but your city names have space in them...and it doesn't appear to have a space between zip and state

    And some zips are 9 while others are 5....


    The only constant appears to be the space in front of the state....

    So....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE MyTable99(Col1 varchar(8000))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'SOUTH EL MONTE CA91733617' UNION ALL 
    SELECT 'BOSSIER LA71172'	   UNION ALL
    SELECT 'GARDENA CA90249107'	   UNION ALL
    SELECT 'MILWAUKEE WI53216'	   UNION ALL
    SELECT 'PARIS IL61944'		   UNION ALL
    SELECT 'DUQUOIN IL62832'	   UNION ALL
    SELECT 'REDWOOD FALLS MN56283'	   UNION ALL
    SELECT 'AUBURN ME04210'
    GO
    
    SELECT    REVERSE(SUBSTRING(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))+1, LEN(Col1)-CHARINDEX(' ',REVERSE(Col1))+1)) AS City
    	, SUBSTRING(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))-2,2) AS State
    	, REVERSE(LEFT(REVERSE(Col1),CHARINDEX(' ',REVERSE(Col1))-3)) AS Zip
      FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use function RIGHT( character_expression, integer_expression) and reverse is not needed in this case.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by snail
    You could use function RIGHT( character_expression, integer_expression) and reverse is not needed in this case.

    For a length of what?

    What offset?

    zip length is variable....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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