Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Red face Unanswered: Need help.. Substrings. Ugh!

    Hi Guys,
    I'm fairly beginner when it comes to sql so reaching out for some help.
    I have a column that contains names such as
    Betty White
    Trump, Donald

    I need to create a mnemonic column. The mnemonic must be first 3 letters of last, first 3 letters of first. Such as:
    WHIBET
    TRUDON

    I can do it in multiple steps... but I need to do it in a simple select statment.
    Could anyone help me with that?
    MUCH appreciated and thanks in advance for your time!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dore0021 View Post
    I can do it in multiple steps... but I need to do it in a simple select statment.
    okay, show the multiple steps, and i'll show you how to combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    7

    Script

    Very crude... I apologize in advance.

    ---Add required fields to create mnemonic---
    alter table vendor add firstname varchar (100),lastname varchar (100),mnemonic varchar (6)
    --select F31,firstname,lastname,mnemonic from vendor
    --select F31,firstname,lastname from vendor where F31 like '%,%'--431

    ---Seperate name, firstname, lastname ---
    --select F31,charindex(',',F31),substring(F31,1,charindex(' ,',F31)),substring(F31,charindex(',',F31)+1,55)fro m vendor where F31 like '%,%'--431charindex(' ,',F31)> 0
    update vendor set firstname = substring(F31,charindex(',',F31)+1,55),lastname = substring(F31,1,charindex(',',F31))where F31 like '%,%'
    update vendor set lastname =replace(lastname,',',' ')

    --select F31,charindex(' ',F31),substring(F31,1,charindex(' ',F31)),
    --substring(F31,charindex(' ',F31)+1,55)from vendor where F31 not like '%,%'--431charindex(' ,',F31)> 0
    update vendor set firstname = substring(F31,1,charindex(' ',F31)),lastname = substring(F31,charindex(' ',F31)+1,55)where F31 not like '%,%'

    ---Checked for lastnames which start with MRS/MR/MS---
    --select * from vendor where lastname like 'MRS%'
    --select * from vendor where lastname like 'MR%'
    --select * from vendor where lastname like 'MS%'
    update vendor set lastname = 'HUNTER',firstname = 'LINDA' where lastname like 'MS%'

    ---Remove Spaces from firstname as a result of seperation---
    update vendor set firstname = dbo.adapt(firstname)

    --select left(lastname,3)+left(firstname,3),lastname,firstn ame from vendor

    ---Create Mnemonic---
    update vendor set mnemonic = left(lastname,3)+left(firstname,3)


    ----------------------
    ---dbo.adapt function---
    ----------------------
    USE [Chantale_test]
    GO
    /****** Object: UserDefinedFunction [dbo].[adapt] Script Date: 04/11/2012 10:55:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE function [dbo].[adapt] (@text nvarchar(500)) returns nvarchar(500)
    as
    begin
    declare @result nvarchar(500)
    set @text=ltrim(rtrim(isnull(@text,'')))
    set @result=''
    if @text>''
    begin
    set @result=@text
    declare @i as int, @c as char(1)
    set @i=1
    while @i<=len(@text)
    begin
    set @c=substring(@text,@i,1)
    if @c in ('','','','','','')
    set @result=replace(@result,@c,'a')
    if @c in ('c','c','c','')
    set @result=replace(@result,@c,'c')
    if @c in ('','','','')
    set @result=replace(@result,@c,'e')
    if @c in ('','','','')
    set @result=replace(@result,@c,'i')
    if @c in ('n','')
    set @result=replace(@result,@c,'n')
    if @c in ('','','','','')
    set @result=replace(@result,@c,'o')
    if @c in ('','','','')
    set @result=replace(@result,@c,'u')
    if @c=''
    set @result=replace(@result,@c,'y')
    if @c not in ('0','1','2','3','4','5','6','7','8','9','q','w',' e','r','t','y','u','i','o','p','a','s','d','f','g' ,'h','j','k','l','z','x','c','v','b','n','m')
    set @result=replace(@result,@c,'')
    set @i=@i+1
    set @result=upper(@result)
    end
    end
    return @result
    end

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So your names are stored as single strings, rather than as separate components?
    I've attached a function that might help you. Use it to parse out the FirstName and LastName from your strings, and then just use the LEFT() function to get the first three characters.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Apr 2012
    Posts
    7
    Hmmm the person I'm helping didn't want to ad a function onto this LIVE db.
    If there is any other way in one select statement to do this, please let me know :-) Thanks again!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the sample data you gave had only two formats

    now i see that there are actually many different formats

    catering for all of them with a series of CASE expressions in a single update statement is still possible, but now it's insanely complicated

    tell us again why you can't do a series of updates?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dore0021 View Post
    Hmmm the person I'm helping didn't want to ad a function onto this LIVE db.
    If there is any other way in one select statement to do this, please let me know :-) Thanks again!
    Tell you what, take a look at my function and if you can find a way to encapsulate all that comprehensive logic into a single command, please send it to me when you are done!

    There's no reason you can't add a function to a database. The code is open for review.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Apr 2012
    Posts
    7

    Thanks!

    Thanks for all your help everyone!
    My coworker asked me to create him a select statement that will show the new mnemonic. I told him that I didn't think it was (easily) possible. I provided him the alternative that I pasted in and said clean the spreadsheet in a test db, then import it into live and run your select.
    He insisted there must be a way... so I just thought I'd check with some experts.
    Thanks again for taking a look!
    Chantale

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by dore0021 View Post
    I have a column that contains names such as
    Betty White
    Trump, Donald

    I need to create a mnemonic column. The mnemonic must be first 3 letters of last, first 3 letters of first. Such as:
    WHIBET
    TRUDON
    If those were covered all cases(i.e. first name and last name only. No others. separated by a blank or comma and a blank", "),
    how about this?
    (Not tested.)
    Code:
    SELECT name
         , UPPER(
              CASE
              WHEN CHARINDEX(',' , name) > 0 THNE
                   LEFT(name , 3) || SUBSTRING(name , CHARINDEX(',' , name) + 2 , 3)
              ELSE SUBSTRING(name , CHARINDEX(' ' , name) + 1 , 3) || LEFT(name , 3)
              END
           ) AS mnemonic
     FROM  test_data

  10. #10
    Join Date
    Apr 2012
    Posts
    7

    :-)

    Thats exactly what I'm looking for!
    The error I get when I run it is this:
    Msg 402, Level 16, State 1, Line 1
    The data types nvarchar and nvarchar are incompatible in the boolean OR operator.
    **pls note, I've removed some pipes where they were double. E.g. || changed to |. As it had given me an incorrect syntax error.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dore0021 View Post
    **pls note, I've removed some pipes where they were double. E.g. || changed to |. As it had given me an incorrect syntax error.
    no, you will want to use plus signs instead of double pipes, as this is microsoft sql server

    poor tonkuma gave you standard sql concatenation operator
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2012
    Posts
    7

    Many Thanks!

    It worked!!!
    Thank you guys so much. I really do appreciate it.


    I wish I could help you in return... but um... hahaa as you can see my skills are pretty basic.

    Thanks again.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll be fine with that solution until someone enters a name like "Bob Smith Jr." or "Smith, Robert T. III" or "Dr. Robert James Smith, MD".
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Apr 2012
    Posts
    7
    95% will be fine though, so I'll take it! It was exactly what we were looking for. Great forum!

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but won't that make the 1 percenters angry. Obama Nation doesn't want us to do that
    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.

Tags for this Thread

Posting Permissions

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