Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Unanswered: SQL String Manipulations

    Hi,

    Could any one please let me know whether following scenario can be implemented in SQL Server?

    Scenario:
    Have a Last Name in a table and which have Last names like,

    SMITH MD
    GEORGE MD PHD
    SMITH MD LLC
    SMTITH JR MD
    SMITH III MD PA
    SMITH,MD

    In other words, last name many titles or suffix. I want to clean this data using SQL. Currently I am doing it in multiple views like, first remove only MD then in next view LLC or JR and so on.

    Here my question is:
    Can I dump all these possible suffix /title into a table except last name and check last name again that table, if that sting is with lastname then I can remove that from last name.

    Something like,

    LOCATE(a.LastName, IN (Select Suffix/Title from Code table))>0
    Then
    Remove that whole sting again looking at the codes table.

    Ex:
    Last name is like SMITH MD

    Codes table will have 2 columns as
    Code Logic
    MD SUBSTRING(a.LastName,1,length(a.NEWlastname)-2)

    to remove that MD for the last name and dump the cleaned data into separate table. So I can use data from that new table for my futher processing.

    Please let me know on the same.

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm sure I've come up with better solutions to this problem in the past...

    As a starter for 10 try this recursive CTE method:
    Code:
    DECLARE @people table (
       name varchar(30)
    );
    
    INSERT INTO @people (name)
      VALUES ('Smith MD')
           , ('George MD PHD')
           , ('SMITH MD LLC')
           , ('Smith JR MD')
           , ('Smith III MD PA')
           , ('Smith MD')
    
    DECLARE @suffixes table (
       suffix varchar(3)
    );
    
    INSERT INTO @suffixes (suffix)
      VALUES ('MD')
           , ('PHD')
           , ('LLC')
           , ('JR')
           , ('III')
           , ('PA');
    
    ; WITH replacements AS (
      SELECT ' ' + suffix As suffix
           , Row_Number() OVER (ORDER BY suffix) As row_num
      FROM   @suffixes
    )
    , recursive_cte AS (
      SELECT name As original_name
           , Cast(name As varchar(max)) As name
           , 1 As level
      FROM   @people
    
        UNION ALL
    
          SELECT recursive_cte.original_name
               , Replace(recursive_cte.name, replacements.suffix, '')
               , recursive_cte.level + 1
          FROM   recursive_cte
           INNER
            JOIN replacements
              ON replacements.row_num = recursive_cte.level
    )
    SELECT *
    FROM   recursive_cte
    WHERE  EXISTS (
             SELECT Max(row_num) + 1
             FROM   replacements
             HAVING Max(row_num) + 1 = recursive_cte.level
           )
    Not happy with it, but it works.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try the attached function.

    Syntax:
    --FormatName decodes a NameString into its component parts and returns it in a requested format.
    --@NameString is the raw value to be parsed.
    --@NameFormat is a string that defines the output format. Each letter in the string represents
    --a component of the name in the order that it is to be returned.
    -- [H] = Full honorific
    -- [h] = Abbreviated honorific
    -- [F] = First name
    -- [f] = First initial
    -- [M] = Middle name
    -- [m] = Middle initial
    -- [L] = Last name
    -- [l] = Last initial
    -- [S] = Full suffix
    -- [s] = Abbreviated suffix
    -- [.] = Period
    -- [,] = Comma
    -- [ ] = Space
    --Sample Syntax : select dbo.FormatName('President Barack Hussein Obama Senior', 'h. F m. L s.')
    --Returns : 'Pres. Barack H. Obama Sr.'
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2013
    Posts
    4

    It's not working

    Hi George,
    Thanks for your reply.
    It's working for values "MD" only, but not for
    MD LLC
    JR MD
    III MD

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a play with the code... There's a @suffixes table that you might want to add to
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2013
    Posts
    1
    you can try the code below:


    create table #name (
    name varchar(30)
    );

    INSERT INTO #name (name)
    VALUES ('Smith MD')
    , ('George MD PHD')
    , ('SMITH MD LLC')
    , ('Smith JR MD')
    , ('Smith III MD PA')
    , ('Smith,MD')


    create function dbo.InlineMax(@val1 int, @val2 int)
    returns int
    as
    begin
    if @val1 > @val2
    return @val1
    return isnull(@val2,@val1)
    end

    select SUBSTRING(name, 1, dbo.InlineMax(charindex(' ', name),CHARindex(',',name))-1) as LastName from #name

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    another idea, might be to find where you have data after a space??? think locate and substring

Posting Permissions

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