Need Help TSQL Code.

Question

Need Help TSQL Code.

rocky_498

SSCertifiable

Points: 6532
More actions
July 30, 2013 at 2:54 pm

#275744

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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

I think this is a slightly shorter solution.

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

rocky_498 SSCertifiable Points: 6532 More actions · Answer 3

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...

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 4

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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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

rocky_498 SSCertifiable Points: 6532 More actions · Answer 6

rocky_498

SSCertifiable

Points: 6532

July 30, 2013 at 3:54 pm

#1637158

Awesome,

That's what I want...

Thank You!

geoff5 Hall of Fame Points: 3560 More actions · Answer 7

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 8

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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 10

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)

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 11

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)