Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2007
    Posts
    1

    Unanswered: How to parse field "LastName,FirstName"

    Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select substring(name,charindex(',',name)+1,len(name))
           + case when charindex(',',name) > 0
                  then ',' + left(name,charindex(',',name)-1) 
                  else '' end as firstname_lastname
      from Customer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    better yet would be to separate FirstName and LastName into separate columns...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jezemine
    better yet would be to separate FirstName and LastName into separate columns...
    I'd bet that separate columns wasn't in IronGiant's assignment.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm curious as to why there was supposed to be a comma between firstname and lastname -- usually it's a space when they're in their "normal" order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Look up with wildcards

    Hi all I have a stored procedure that when the users type in the last name or the starting letters of the last name they get the result. What I would like for them to be able to do is enter in a wildcard and get the results also. Who would I do that

    Code:
    CREATE PROCEDURE [dbo].[AdvSrchSubjectsLastName]
    
    (@Enter_SubjLastName nvarchar(50))
    
    AS
    SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
    WHERE ((SubjLastName LIKE @Enter_SubjLastName + '%'))

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so if they enter a wildcard, you want to be able to return what, exactly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    sorry

    Sorry, if they enter a * they will get all of the records

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, in that case all you need is a line in your proc which replaces the * with %

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    ok

    there is no * in my procedure

    Code:
    CREATE PROCEDURE [dbo].[AdvancedSrch_VictimLastName](@Enter_LastName nvarchar(50))
    
    AS
     SELECT      dbo.Security_Tbl.[Secuirty I/RDocument], dbo.Security_Tbl.[Security IR Number], dbo.SecurityVictims_Tbl.[First Name], 
                            dbo.SecurityVictims_Tbl.[Last Name], dbo.Security_Tbl.[Violation Type]
    FROM          dbo.Security_Tbl INNER JOIN
                            dbo.SecurityVictims_Tbl ON dbo.Security_Tbl.[Security IR Number] = dbo.SecurityVictims_Tbl.[Security IR Number]
    WHERE      (dbo.SecurityVictims_Tbl.[Last Name] LIKE @Enter_LastName + N'%')
    see

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i know

    i was referring to this asterisk here --
    Quote Originally Posted by desireemm
    ... if they enter a *
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok sorry

    so do you mean
    Code:
    CREATE PROCEDURE [dbo].[AdvSrchVictAll](@Enter_LastName nvarchar(50))
    
    AS
     SELECT      dbo.Security_Tbl.[Secuirty I/RDocument], dbo.Security_Tbl.[Security IR Number], dbo.SecurityVictims_Tbl.[First Name], 
                            dbo.SecurityVictims_Tbl.[Last Name], dbo.Security_Tbl.[Violation Type]
    FROM          dbo.Security_Tbl INNER JOIN
                            dbo.SecurityVictims_Tbl ON dbo.Security_Tbl.[Security IR Number] = dbo.SecurityVictims_Tbl.[Security IR Number]
    WHERE      (dbo.SecurityVictims_Tbl.[Last Name] LIKE @Enter_LastName + '*')

    LIKE @Enter_Lastname + '*'

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not what i meant

    something like...

    CREATE PROCEDURE ...
    AS
    SET @Enter_LastName = REPLACE(@Enter_LastName,'*','%')
    SELECT ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking thank R937

    Thank you for being so patient with me, sorry I understand now

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ahh

    this is what I needed, thanks guys

    Code:
    ALTER PROCEDURE [dbo].[AdvSearchRevocations]
    
       (  @Enter_LastName nvarchar(25)  )
    
    AS 
    
       IF patindex( '*', @Enter_LastName ) > 0 
    
          BEGIN
    
             SET @Enter_LastName = replace( @Enter_Lastname, '*', '%' ) 
    
             SELECT LastName,FirstName, ReasonOfRevocation, TM#, [I/R #], Date
    
             FROM dbo.Revocations_Tbl
    
             WHERE LastName LIKE @Enter_LastName
    
          END
    
       ELSE
    
          BEGIN
    
             SELECT LastName, FirstName, ReasonOfRevocation, TM#, [I/R #], Date
    
             FROM dbo.Revocations_Tbl
    
             WHERE LastName = @Enter_LastName
    
          END
    
    GO

Posting Permissions

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