Results 1 to 6 of 6

Thread: Query question

  1. #1
    Join Date
    Sep 2010
    Location
    Orange County, CA
    Posts
    2

    Unanswered: Query question

    First off I am using SQL Server 2008. (Not R2)

    I have a query in which I need to analyze the contents of a single field (string comparison), because an import that was done two or more fields were merged into a single field.

    Anyways, I want to analyze the contents of this particular field so I can update the two fields to separate it into two, or three.

    I am looking for a space in this particular field.

    There are 426,000 records in this table, and 117,000 of them are in the result set of my query.

    Here is my query:
    select firstname, middlename, fullname,
    LEFT(firstname,(PATINDEX('% %',firstname)-1)) as 'First Name',
    RIGHT(firstname,len(Substring(firstname,(PATINDEX( '% %',firstname)),1000))) as 'Middle Name',
    PATINDEX('% %',firstname)as'Space Position'
    from Contact where PATINDEX('% %',firstname)>0

    However, I have discovered in analyzing the data, that in some instances of the contents of the data, there are some instances where there are more than 1 space.

    So the First name field not only contains a middle initial, it also contains a title in some cases.

    I want to know how many times this occurs in my 117,000 record dataset.

    I want to analyze the values of the First Name fields, and find out the count of the number of spaces included in the results, so I can parse it out and properly deal with the data based on the values and the number of spaces.

    If it has more than one space, and it includes the last name, instead of the title.

    Different rules based on the different results.

    So let me get to the question.

    Can I build a function that is designed to look at this character string, count the number of spaces, display them in my result set, and use the count of the number of spaces as part of my where clause in my query?

    Or am I over thinking this and over complicating it?

    This is what happens when we trust sales people to be data entry people. Hehe...no offense sales guys....

    Can I call the function in my select clause, potentially as a variable (if required)?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what about using the replace function

    replace(data," "," ")

    What about just Spliting the data by the Space then trim

    found this SQL Server Helper - Tips and Tricks - Split Name
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this function for splitting fullname fields into individual components:
    http://dl.dropbox.com/u/2507186/Func...FormatName.sql
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    blindman that is a cool script

    good work
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Necessity was the mother of invention.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2010
    Location
    Orange County, CA
    Posts
    2
    Thank you both for your help, that worked perfectly!

Posting Permissions

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