tsql help

Question

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: 442467 More actions · Answer 1

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

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: 286590 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: 286590 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'

)

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: 442467 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'

SELECT MyColumnName FROM #mytable

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

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

DROP TABLE #mytable