#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Location
    St. Louis
    Posts
    235
    Rep Power
    27

    Finding comma in string and parsing out data to the left or right of it


    Hi Guys,

    Need some help again.

    I have two different functions I need to get working.

    I have a string.

    -------------------------------------

    First, What I need to do is find the first instance of a comma ',' and then look for an instance of 2 characters right after it.

    For instance

    Bla bla bla, XX bla bla bla bla

    or

    bla bla bla,XX bla bla bla

    I would need to grab that XX and nothing else.

    My goal is to parse out a state abbreviation

    Aka

    St. Louis, MO L:KJASFLKJSD:LKJS

    Would return MO

    --------------------------------------------

    Second, I need to again find the first instance of a comma, and then capture everything to the left of it.

    My goal is to parse out a possible city name

    Aka

    St. Louis, MO L:KJASFLKJSD:LKJS

    Would return 'St. Louis'
    -----------------------------------------

    What would be the best method to do both of these? I'm looking through some code that does something similar. But I'm a little confused by it.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    your second one is the easier of the two

    <CFSET city = ListFirst(yourstring) >

    this works because the default list delimiter is a comma

    your first one can be done by taking the second list item in the string, trimming it (to get rid of the possible space), and then taking the left 2 characters

    <CFSET state = Left(Trim(ListGetAt(yourstring,2)),2) >

    simple, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Location
    St. Louis
    Posts
    235
    Rep Power
    27
    Thanks! Seriously.

    So for the second one

    <CFSET state = Left(Trim(ListGetAt(yourstring,2)),2) >

    I actually need the two characters to the right, so I guess that would need to be right(trim(list.....

    However, I ONLY want to try and get the 2 characters if it's actually just two characters.

    So, if I have

    st. louis, MO 63101

    there is a space after the 2 characters. So that tells me it's a block of 2 characters. I would want to get that 'MO' from the string.

    However, if I have

    St. Louis, MOS 63101

    That 'MOS' wouldn't qualify since it's a block of 3, therefore it's not a state. The reason is because if someone were to put

    St. Louis, MONTERREY 63101

    the first two characters after the comma would yield MO, which is a state. I don't want that to happen.

    How could I do that?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by techboyjk
    <CFSET state = Left(Trim(ListGetAt(yourstring,2)),2) >

    I actually need the two characters to the right, so I guess that would need to be right(trim(list.....
    no, sorry, you don't want to use Right()

    i guess you didn't see how i was using ListGetAt()?

    ListGetAt(...2) extracts everything after the first comma, up to the second, if any
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Location
    St. Louis
    Posts
    235
    Rep Power
    27
    Ok thanks!

    Since

    Left(Trim(ListGetAt(string,2)),2)

    Will take away the space after the comma and grab the next two characters, I need to find a way to determine if I want those two characters or not.

    I only want to grab that if the string after the comma is just two characters in lenth. If it is less, or more than 2, then it doesn't qualify.

    So I'm thinking I should, trim the space after the comma, then check if the first and second positions after the comma are alphabetical characters or not. If this is true, then check to see if the 3 position is a space. If it is not a space, check to see if that is the total length of the string. If that is true, then I truly have a situation like

    "st. louis, MO"

    or

    "St. Louis, MO 63101"

    but it will also not accept something like

    "St. Louis, MON 63101"

    What would be the best way to do that?

    -remove any space after comma between following character
    -check if 1st position after comma is alphabetical
    -check if 2nd position after comma is alphabetical
    -check if 3rd position is space or even exists

    if match, query zip code db for state abbreviation match

    I'm looking through available string functions to see if I can find the best way. But if anybody has a good solution, please let me know! Thanks!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Location
    St. Louis
    Posts
    235
    Rep Power
    27
    Ok, I got most of it down. I just need to figure out the following

    1. Given String like 'St. Louis, MO adsfadf'

    Trim any space after first comma, check first and second position for alphabetical, check if 3rd position exists, check third position for space"


    2. Given String like 'St. Louis, Missouri adsfadf'

    Get string after comma, remove space after comma. Parse out first part of string (using spaces as delimiter), check if match. If not parse out first and second part of string (for cases like 'north carolina') check if match.

    ex 'St. Louis, Missouri adsfasf' yields 'Missouri' and would yield a match.
    ex. 'St. Louis, West Virginia' yields 'west' and does not find match. 2nd attempted yields 'West Virginia' and does find match.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    you're doing great, don't give up

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Meatball Surgeon
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 2004
    Location
    Elbow deep in code
    Posts
    2,056
    Rep Power
    1321
    Use Find() to find where the cursor is and then use mid() to get the last part of the string. Then use replace() to remove the spaces.
    Three gigs for the secretaries fair
    Seven gigs for the system source
    Nine gigs for the coders in smoky lairs
    One disk to rule them all, one disk to bind them
    One disk to hold the files, and in the darkness grind'em
    ---------------------------------------------------
    It is by caffeine alone that I set my mind in motion.
    It is by the beans of Java, that my thoughts acquire speed.
    The hand acquire shakes; the shakes become a warning.
    It is by caffeine alone that I set my mind in motion.

IMN logo majestic logo threadwatch logo seochat tools logo