Results 1 to 4 of 4

Thread: Part of string

  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: Part of string

    Need help..

    I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?

    Thanx

  2. #2
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    try this

    select substring('last,first',1,charindex('first,last'))

    i hav'nt checked it.. I think u may have to alter it a little
    Sudar

    --
    My Blog

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Assuming there is always a comma

    declare @name char(30)
    set @name = 'GATES, BILL'
    select left(@name,charindex(',',@name)-1)

    returns

    GATES

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.

    Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.


    create function FormatName(@NameString varchar(100), @NameFormat varchar(20))
    returns varchar(100) as
    begin
    --blindman, 11/04
    --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

    --Test variables
    -- declare @NameString varchar(50)
    -- declare @NameFormat varchar(20)
    -- set @NameFormat = 'L, h. F m. s.'
    -- set @NameString = 'Father Gregory Robert Von Finzer Jr'

    Declare @Honorific varchar(20)
    Declare @FirstName varchar(20)
    Declare @MiddleName varchar(30)
    Declare @LastName varchar(30)
    Declare @Suffix varchar(20)
    Declare @TempString varchar(100)
    Declare @IgnorePeriod char(1)

    --Prepare the string
    --Make sure each period is followed by a space character.
    set @NameString = rtrim(ltrim(replace(@NameString, '.', '. ')))
    --Eliminate double-spaces.
    while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ')
    --Eliminate periods
    while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')

    --If the lastname is listed first, strip it off.
    set @TempString = rtrim(left(@NameString, charindex(' ', @NameString)))
    if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2)))
    if right(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1)
    if len(@LastName) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

    --Get rid of any remaining commas
    while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')

    --Get Honorific and strip it out of the string
    set @TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
    if @TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @Honorific = @TempString
    if len(@Honorific) > 0 set @NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))

    --Get Suffix and strip it out of the string
    set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
    if @TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString
    if len(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))

    if @LastName is null
    begin
    --Get LastName and strip it out of the string
    set @LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
    set @NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName)))
    --Check to see if the last name has two parts
    set @TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' ')))
    if @TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
    begin
    set @LastName = @TempString + ' ' + @LastName
    set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
    end
    end

    --Get FirstName and strip it out of the string
    set @FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' ')))
    set @NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))

    --Anything remaining is MiddleName
    set @MiddleName = @NameString

    --Create the output string
    set @TempString = ''
    while len(@NameFormat) > 0
    begin
    if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.'
    begin
    set @IgnorePeriod = 'F'
    set @TempString = @TempString +
    case ascii(left(@NameFormat, 1))
    when '72' then case @Honorific
    when 'Dr' then 'Doctor'
    when 'Rev' then 'Reverend'
    when 'Hon' then 'Honorable'
    when 'Maj' then 'Major'
    when 'Pvt' then 'Private'
    else isnull(@Honorific, '')
    end
    when '70' then isnull(@FirstName, '')
    when '77' then isnull(@MiddleName, '')
    when '76' then isnull(@LastName, '')
    when '83' then case @Suffix
    when 'Jr' then 'Junior'
    when 'Sr' then 'Senior'
    when 'Esq' then 'Esquire'
    else isnull(@Suffix, '')
    end
    when '104' then case @Honorific
    when 'Doctor' then 'Dr'
    when 'Reverend' then 'Rev'
    when 'Honorable' then 'Hon'
    when 'Major' then 'Maj'
    when 'Private' then 'Pvt'
    else isnull(@Honorific, '')
    end
    when '102' then isnull(left(@FirstName, 1), '')
    when '109' then isnull(left(@MiddleName, 1), '')
    when '108' then isnull(left(@LastName, 1), '')
    when '115' then case @Suffix
    when 'Junior' then 'Jr'
    when 'Senior' then 'Sr'
    when 'Esquire' then 'Esq'
    else isnull(@Suffix, '')
    end
    when '46' then case right(@TempString, 1)
    when ' ' then ''
    else '.'
    end
    when '44' then case right(@TempString, 1)
    when ' ' then ''
    else ','
    end
    when '32' then case right(@TempString, 1)
    when ' ' then ''
    else ' '
    end
    else ''
    end
    if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER'))
    or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III')))
    set @IgnorePeriod = 'T'
    end
    set @NameFormat = right(@NameFormat, len(@NameFormat) - 1)
    end

    -- select ltrim(rtrim(@TempString))

    Return @TempString
    end
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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