Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unhappy Unanswered: SQL VIEW to strip characters

    I am trying to create a view of a table that will strip off characters in a certain field and I am not sure where to even start with this.

    For example I have a first name field with various types of entries:
    Jane D.
    David
    Amy B
    Jackie Smith

    I need for the view to pull out just the first name, but strip off anything after, so technically anything after the first space.

    I would expect my results from above to be:
    Jane
    David
    Amy
    Jackie

    Can anyone point me in the right direction?

    Thank You

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How about
    Code:
    select  substring (name, 1, convert(int, replace( convert(varchar,charindex(' ',name)), convert(varchar,0), convert(varchar,len(name))))) as fname  from your table
    [edit]
    select substring (name, 1, convert(int,replace( charindex(' ',name), 0, len(name)))) as fname from [your table]
    [/edit]

    This is the best i can do at the moment ..
    btw .. time to go home
    Last edited by Enigma; 11-18-03 at 21:51.
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Try this using pubs:

    select case when (len(left(au_lname,charindex(' ',au_lname))) > 0)
    then left(au_lname,charindex(' ',au_lname))
    else au_lname end from pubs.dbo.authors

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    better still
    Code:
    select case when charindex(' ',au_lname) = 0
    then au_lname
    else left(au_lname,charindex(' ',au_lname))  end from pubs.dbo.authors
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Simplicity rules.

  6. #6
    Join Date
    Nov 2003
    Posts
    10

    Thanks...

    Thank You all sooooo much for the suggestions, I got the VIEW to filter as I expected using the syntax below.

    select case when charindex(' ',au_lname) = 0
    then au_lname
    else left(au_lname,charindex(' ',au_lname)) end from pubs.dbo.authors

    I wanted to ask if you could give me a brief understanding of what this is doing (even if you want to direct me to a resource to understand further). I just want to make sure I understand what I did and why it worked this way. Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd avoid CASE statements. I don't think they are as efficient as functions.

    select left(au_lname, charindex(' ', au_lname + ' ') -1) from pubs.dbo.authors

    blindman

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Blindman - Do you have documentation or any performance data information concerning using the case statement ?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No I don't. Hence my qualified statement. I just suspect this because it implies logical branching, which seems at odds with SQL Server's strength in set based operations. It's probably worth looking into, because its a coding choice that comes up frequently.

    blindman

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    I was curious if you had any information concerning this because one of the sql server instances I worked on was over 10 terabytes and used case statements everywhere without any noticeable performance impact. I know that ss will flatten out a query - rearrange the logic to optimize it.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't find any documentation, except for a few web sites that point out that using a CASE statement increases efficiency if it eliminates multiple table or index scans.

    I ran these two statements on a table with 639,000 rows. I ran each statement eight consecutive times, dropping the first three data points to account for caching.

    A)
    select left(AccountName, charindex(' ', AccountName + ' ') -1) String into #Test from Account

    B)
    select case when charindex(' ',AccountName) = 0 then AccountName else left(AccountName,charindex(' ',AccountName)) end String into #Test from Account

    The milliseconds required for the first statement were (1186, 1233, 1156, 1123, 1266) for an average of 1192.8

    The milliseconds required for the second statement were (1483, 1610, 1516, 1543, 1563) for an average of 1543.0

    Even with only five datapoints these population means are six standard deviations appart.

    blindman

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    I was not disputing the example you posted as being faster (I actually expected to see more of a performance impact between the 2) only your statement concerning avoiding case statements.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh heck, I use CASE statements all the time. Just not when I can do the same thing using a function string.

    On rare occasions I even use Cursors and dynamic SQL. Please don't tell anyone!

    blindman

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    Cursors - Oyyyyy !

    Dynamic sql - just make sure you visit your doctor and get your injection - oh wait, ignore that, we don't want injections.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rnealejr
    Cursors - Oyyyyy !

    Dynamic sql - just make sure you visit your doctor and get your injection - oh wait, ignore that, we don't want injections.
    A shot?

    Make mine a double!

    [8-)]
    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
  •