comma seperated name question

  • Hi All:

    I know that I’ve seen in a post in the past, but I can’t find it

    This is what I need to do

    I have a table that contains salesperson information on one system and another system has a table that contains salesperson information as well. We want to maintain salesperson information on only one system and have a job that updated the other system on a nightly basis.

    Here is the question

    On system A (source) the table looks like this:

    Sales_ID

    Sales_name

    The sales_name is entered as last name, first name. On system B (destination) the table looks like

    Sales_ID

    First_Name

    Last_Name

    So I need to take the sales_name and split it at the comma. Something like

    Some code as First_Name

    Some code as Last_Name

    Thanks for your help

    William

  • This assumes that you have no middle name, not mrs, ms, jr, snr....

     

    DECLARE @Name AS VARCHAR(75)

    SET @Name = 'RGRus, Ninja'

    SELECT LEFT(@Name, CHARINDEX(',', @Name) - 1) AS LastName, RIGHT(@Name, LEN(@Name) - CHARINDEX(',', @Name) - 1) AS FirstName

  • Try something like this:

    declare

    @varStr varchar(255)

    set

    @varStr = 'Jones, Tom'

    select

    substring(@varStr, 1, patindex('%,%', @varStr) - 1) as LastName,

    ltrim(substring(@varStr, patindex('%,%', @varStr) + 1, len(@varStr) - patindex('%,%', @varStr))) as FirstName

  • Thanks guys

    Both solutions worked Great!!!!

    William

  • Ninja,

    I keep forgetting about the right() function.

  • There's not right answer here... they but work .

  • We do the best we can with what we have.

  • Probably it's for good.

    SUBSTRING works a little bit faster.

    _____________
    Code for TallyGenerator

  • Nice to know. Thanx.

Viewing 9 posts - 1 through 8 (of 8 total)

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