Flip two words

Question

Flip two words

sharonsql2013

SSCertifiable

Points: 5908
More actions
May 14, 2014 at 9:59 am

#285506

In order to join two tables , I need to flip the name column in one table to bring the last name first.
So If its Mike Davis
I need it to be Davis, Mike
How can I achieve this? P.S. Mike Davis is in one column

Viewing 14 posts - 1 through 13 (of 13 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

sharonsql2013 (5/14/2014)
In order to join two tables , I need to flip the name column in one table to bring the last name first.
So If its Mike Davis
I need it to be Davis, Mike
How can I achieve this? P.S. Mike Davis is in one column

UGH. It is really unfortunate you don't have your data normalized. Why are you storing a person's name in more than 1 table? Doing a join like this is a clear indication that there are some major normalization issues going on here.

You can use some string manipulation to kludge this together but don't expect anything resembling decent performance.

declare @FullName varchar(25) = 'Mike Davis'

select SUBSTRING(@FullName, charindex(' ' , @FullName, 0) + 1, len(@FullName)) + ', ' + LEFT(@FullName, charindex(' ' , @FullName, 0) - 1)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Great!. Works but then I get an error : Invalid length parameter passed to the left or Substring function

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

sharonsql2013 (5/14/2014)
Great!. Works but then I get an error : Invalid length parameter passed to the left or Substring function

That is because you have at least one value that doesn't have a space. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Here is one way you might be able to deal with that.

with Names as

(

select 'Mike Davis' as FullName union all

select 'NoSpace'

)

select case when charindex(' ' , FullName, 0) > 0 then

SUBSTRING(FullName, charindex(' ' , FullName, 0) + 1, len(FullName)) + ', ' + LEFT(FullName, charindex(' ' , FullName, 0) - 1)

else FullName end

from Names

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

For fun:-P, it always looks simple until one starts to meddle with it

😎

with Names as

(

select 'Mike Davis' as FullName union all

select 'NoSpace' union all

select ' trailing and leading ' union all

select ' leading space' union all

select ' ' union all

select ' 3' union all

select '2 ' union all

select ' 1 ' union all

select 'trailing space '

)

select case when charindex(' ' , FullName, 0) > 0 then

SUBSTRING(FullName, charindex(' ' , FullName, 0) + 1, len(FullName)) + ', ' + LEFT(FullName, charindex(' ' , FullName, 0) - 1)

else FullName end

from Names

Results

reversed_names

--------------------------

Davis, Mike

NoSpace

trailing and leading ,

leading space,

,

3,

, 2

1 ,

space , trailing

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Eirikur Eiriksson (5/14/2014)
For fun:-P, it always looks simple until one starts to meddle with it
😎

Not a huge deal...just add some ltrim, rtrim.

with Names as

(

select 'Mike Davis' as FullName union all

select 'NoSpace' union all

select ' trailing and leading ' union all

select ' leading space' union all

select ' ' union all

select ' 3' union all

select '2 ' union all

select ' 1 ' union all

select 'trailing space '

)

select case when charindex(' ' , ltrim(rtrim(FullName)), 0) > 0 then

ltrim(rtrim(SUBSTRING(ltrim(rtrim(FullName)), charindex(' ' , ltrim(rtrim(FullName)), 0) + 1, len(ltrim(rtrim(FullName)))) + ', ' + LEFT(ltrim(rtrim(FullName)), charindex(' ' , ltrim(rtrim(FullName)), 0) - 1)))

else ltrim(rtrim(FullName)) end

from Names

Yeah I sure wouldn't allow that gibberish anywhere near a join condition on my system. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mickyT SSChampion Points: 10360 More actions · Answer 7

Hi

I just cringe at the idea of joining on names in the first place. Having a reasonably common name I can see all sorts of problems and have encountered them with some jobs that I have done.

However having said that, this could be another way of attacking the problem

select ID, Name INTO #tab1

FROM (VALUES

(1,'John Smith'),

(2,'Mary Ann Jones'),

(3,'Billy Bob Jnr')

) t1 (ID, Name);

select ID, Name INTO #tab2

FROM (VALUES

(3,'Smith, John'),

(2,'Jones, Mary Ann'),

(1,'Bob, Billy Jnr')

) t2 (ID, Name);

WITH ordName1 AS (

select *

from #tab1 t

CROSS APPLY (SELECT Item + ' ' FROM [dbo].[DelimitedSplit8K](REPLACE(t.Name,',',''),' ') ORDER BY Item FOR XML PATH('') ) ca (o)

),

ordName2 AS (

select *

from #tab2 t

CROSS APPLY (SELECT Item + ' ' FROM [dbo].[DelimitedSplit8K](REPLACE(t.Name,',',''),' ') ORDER BY Item FOR XML PATH('') ) ca (o)

)

select *

from ordName1 o1 inner join ordname2 o2 on o1.o = o2.o;

drop table #tab1;

drop table #tab2;

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 8

Wonderful, much more than I could ask for. Thanks a lot.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Be careful here. Dwain commented that he has encountered significant performance issues when using a value from a function like that as the first parameter in DelimitedSplit8K. I was rather surprised myself but it ended up being better to pull the replace into a cte.

OP, make sure you test this for performance...of course since you are joining on names performance is likely going to be pretty bad no matter how you slice it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

mickyT (5/14/2014)
However having said that, this could be another way of attacking the problem

Until you find that Gordon Abraham and Abraham Gordon (or any other combination of names) are two different people. 😀

The best solution to this, will be normalization.

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

mickyT SSChampion Points: 10360 More actions · Answer 11

mickyT

SSChampion

Points: 10360

May 14, 2014 at 2:01 pm

#1713612

Thanks Sean I'll have to remember that.

mickyT SSChampion Points: 10360 More actions · Answer 12

Luis Cazares (5/14/2014)
mickyT (5/14/2014)
However having said that, this could be another way of attacking the problem
Until you find that Gordon Abraham and Abraham Gordon (or any other combination of names) are two different people. 😀
The best solution to this, will be normalization.

Yep:-D or until you realize that John Smith and John Smith are different people and Bob Jones and Robert Jones are the same person.

I'll repeat ... I cringe at the idea of joining on names

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 13

mickyT (5/14/2014)
Luis Cazares (5/14/2014)
mickyT (5/14/2014)
However having said that, this could be another way of attacking the problem
Until you find that Gordon Abraham and Abraham Gordon (or any other combination of names) are two different people. 😀
The best solution to this, will be normalization.
Yep:-D or until you realize that John Smith and John Smith are different people and Bob Jones and Robert Jones are the same person.
I'll repeat ... I cringe at the idea of joining on names

Can give a potential good laugh:-D with names like

[font="Courier New"]Earl E. Bird

Gail Force

Hy Ball

I.M. Boring

Pepe Roni

Dan Druff[/font]

But it's a pain if used for joining in a database

😎