Need Help TSQL Code.

  • I have source file with column

    Full Name

    Jim k Smith

    Robert K Lin

    Chris Tim

    and My destination has

    FirstName and LastName

    I want to parse FullName

    I know how I can use SSIS Expression to parse the address, Here it is

    FirstName = LTRIM(RTRIM(TRIM(REVERSE(SUBSTRING(REVERSE([Full Name]),1,FINDSTRING(REVERSE([Full Name])," ",1))))))

    LastName = LTRIM(RTRIM(TRIM(SUBSTRING([Full Name],1,FINDSTRING([Full Name]," ",1)))))

    Is any one can help me to transfer in T-SQL?

    Thank You in advance.

  • You need to realize that you might have names that can change the logic. The most common example would be spanish names (which can appear even in an English speaking country).

    However, for the examples you posted, here's an option. Check out that the CTE is just to work with the sample data. Please post it in a consumable format next time.

    WITH CTE AS(

    SELECT 'Jim k Smith' [FullName] UNION ALL

    SELECT 'Robert K Lin' UNION ALL

    SELECT 'Chris Tim'

    )

    SELECT LEFT( FullName, LEN(FullName) - CHARINDEX( ' ', REVERSE(FullName))),

    RIGHT( FullName, CHARINDEX( ' ', REVERSE(FullName)) - 1)

    FROM CTE

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I think this is a slightly shorter solution.

    LastName = reverse(left(reverse(FullName), charindex(' ', reverse(FullName)) - 1))

  • Thank You For your help...

    However I want only FirstName and LastName

    the logic would be for FirstName = From Left to ' ' (First Space)

    the logic would be for LastName = From Rifh to ' ' (First Space)

    Please advise...

  • geoff5 (7/30/2013)


    I think this is a slightly shorter solution.

    LastName = reverse(left(reverse(FullName), charindex(' ', reverse(FullName)) - 1))

    How is that shorter and why do you believe that 3 REVERSEs are better than 1?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Then it's a lot easier.

    WITH CTE AS(

    SELECT 'Jim k Smith' [FullName] UNION ALL

    SELECT 'Robert K Lin' UNION ALL

    SELECT 'Chris Tim'

    )

    SELECT LEFT( FullName, CHARINDEX( ' ', FullName)),

    RIGHT( FullName, CHARINDEX( ' ', REVERSE(FullName)))

    FROM CTE

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Awesome,

    That's what I want...

    Thank You!

  • Jeff,

    I was mistaken because I looked too quickly over the code and didn't read it properly. My code was neither shorter nor simpler.

    I am appropriately abashed.

    Geoff

  • HI Luis, I am a newbie and I have a doubt on your code.

    What is the purpose of the REVERSE() in your code?

    ie. here... REVERSE(FullName)

  • Hi George,

    You should really be trying to figure out this yourself by using BooksOnLine (BOL or SQL Server help) but I'll try to explain.

    REVERSE will do exactly what it says, returns the reverse order of a string value.

    I use it because I need to find the last space in the string. As CHARINDEX won't have a backwards function, I need to reverse the string to get the last space that will become the first one. Was I clear or did I confuse you even more? 😛

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • geoff5 (7/30/2013)


    Jeff,

    I was mistaken because I looked too quickly over the code and didn't read it properly. My code was neither shorter nor simpler.

    I am appropriately abashed.

    Geoff

    No problem. I thought I was missing something. Thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares (8/1/2013)


    Hi George,

    You should really be trying to figure out this yourself by using BooksOnLine (BOL or SQL Server help) but I'll try to explain.

    REVERSE will do exactly what it says, returns the reverse order of a string value.

    I use it because I need to find the last space in the string. As CHARINDEX won't have a backwards function, I need to reverse the string to get the last space that will become the first one. Was I clear or did I confuse you even more? 😛

    To add to that, especially since George is a newbie...

    @george, just in case you don't know (a lot of newbies don't), "Books Online" is the "help" documentation that comes with SQL Server. You can easily get there by pressing the {f1} key from SSMS (SQL Server Management Studio).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply