tsql help

Question

Post reply

tsql help

etirem

SSCommitted

Points: 1655
More actions
November 2, 2011 at 10:38 am

#247268

Hi all,

I have records under columns as:

select top 4 MyColumnName from MyTable

MyColumnName

LN1, FN1

Lastname2, Firstname2

Lastnm3, Fname3

Lastname444, Firstname444

I want the result as:

MyColumnName

FN1 LN1

Firstname2 Lastname2

Fname3 Lastnm3

Firstname444 Lastname444

Thanks in advance!!

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

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

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

November 2, 2011 at 10:42 am

#1402266

Not sure what you are asking. Please read the first article I reference below regarding asking for help. It will show you what we need and how to post it to get the best responses.

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

seth delconte

SSCertifiable

Points: 6388

November 2, 2011 at 10:51 am

#1402271

select top 4 replace(MyColumnName,',','') from MyTable

_________________________________
seth delconte
http://sqlkeys.com

etirem SSCommitted Points: 1655 More actions · Answer 3

Ok...here it is.. hope this is clear..

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

( MyColumnName varchar(50))

INSERT INTO #mytable(MyColumnName)

SELECT 'LN1, FN1' UNION ALL

SELECT 'Lastname2, Firstname2' UNION ALL

SELECT 'Lastnm3, Fname31' UNION ALL

SELECT 'Lastname444, Firstname444'

SELECT MyColumnName FROM #mytable

LN1, FN1

Lastname2, Firstname2

Lastnm3, Fname31

Lastname444, Firstname444

But I need the result set as:

FN1 LN1

Firstname2 Lastname2

Fname31 Lastnm3

Firstname444 Lastname444

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

select substring(MyColumnName,charindex(',',MyColumnName)-1,len(MyColumnName))+' '+substring(MyColumnName,1,charindex(',',MyColumnName)-1) from #mytable

1, FN1 LN1

2, Firstname2 Lastname2

3, Fname31 Lastnm3

4, Firstname444 Lastname444

(4 row(s) affected)

Sorry, didn't notice that you wanted the first and last names transposed... 🙂

_________________________________
seth delconte
http://sqlkeys.com

etirem SSCommitted Points: 1655 More actions · Answer 5

etirem

SSCommitted

Points: 1655

November 2, 2011 at 11:07 am

#1402283

Thank You!!!

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

Something like this?



;with MyTable (BadColumn)

as (

SELECT 'LN1, FN1' UNION ALL

select 'Lastname2, Firstname2' UNION ALL

select 'Lastnm3, Fname3' UNION ALL

select 'Lastname444, Firstname444'

)

select

SUBSTRING(BadColumn, CHARINDEX(',', BadColumn) + 1, DATALENGTH(BadColumn))

+ ' ' + LEFT(BadColumn, CHARINDEX(',', BadColumn) - 1)

,*

from MyTable

Is possible you should consider making FName and LName separate column so you don't have to fight this type of thing.

--Edit looks like Seth posted an answer before I had refreshed. 😀

_______________________________________________________________

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/

etirem SSCommitted Points: 1655 More actions · Answer 7

Sorry! one more thing ....

Below query fails when I have data with only one word as: select 'noname' UNION ALL select 'noname'

select

SUBSTRING(BadColumn, CHARINDEX(',', BadColumn) + 1, DATALENGTH(BadColumn))

+ ' ' + LEFT(BadColumn, CHARINDEX(',', BadColumn) - 1)

,*

from MyTable

;with MyTable (BadColumn)

as (

SELECT 'LN1, FN1' UNION ALL

select 'Lastname2, Firstname2' UNION ALL

select 'Lastnm3, Fname3' UNION ALL

select 'Lastname444, Firstname444' UNION ALL

select 'noname' UNION ALL

select 'noname'

)

Sean Lange SSC Guru Points: 286562 More actions · Answer 8

Just need a case statement like this.



;with MyTable (BadColumn)

as (

SELECT 'LN1, FN1' UNION ALL

select 'Lastname2, Firstname2' UNION ALL

select 'Lastnm3, Fname3' UNION ALL

select 'Lastname444, Firstname444' UNION ALL

select 'noname' UNION ALL

select 'noname' UNION ALL

select 'noname'

)

select

case when CHARINDEX(',', BadColumn) > 0 then

SUBSTRING(BadColumn, CHARINDEX(',', BadColumn) + 1, DATALENGTH(BadColumn))

+ ' ' + LEFT(BadColumn, CHARINDEX(',', BadColumn) - 1)

else BadColumn end

,*

from MyTable

_______________________________________________________________

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/

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 9

Edit: Never mind, I missed the flipping of first and last name as well.

This?



IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

( MyColumnName varchar(50))

INSERT INTO #mytable(MyColumnName)

SELECT 'LN1, FN1' UNION ALL

SELECT 'Lastname2, Firstname2' UNION ALL

SELECT 'Lastnm3, Fname31' UNION ALL

SELECT 'Lastname444, Firstname444' union all

select 'noname' UNION ALL

select 'noname' UNION ALL

select 'noname' 

SELECT MyColumnName FROM #mytable

select REPLACE(MyColumnName,',','') as MyColumnName from #mytable;

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable