5 Replies - 16815 Views - Last Post: 02 December 2008 - 07:54 AM Rate Topic: -----

#1 djs425  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-December 08

SQL - last name, first name reversal

Post icon  Posted 01 December 2008 - 08:02 AM

I have a column in a table that has lastname, firstname and I want to output it as first name last name. Can anyone help with this? Thanks
Is This A Good Question/Topic? 0
  • +

Replies To: SQL - last name, first name reversal

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: SQL - last name, first name reversal

Posted 01 December 2008 - 08:14 AM

What you can do is when you write your report, or write a query, list the first name before the last name. Or you can do something like this
Select ([Table]![FIRST_NME]) & ", " & ([Table]![LAST_NME]) as Name
From Table
Where x=y



To my knowledge, the only other way to do what you want is to have the column names in the exact order you want them.
Was This Post Helpful? 0
  • +
  • -

#6 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL - last name, first name reversal

Posted 01 December 2008 - 12:02 PM

View PostP4L, on 1 Dec, 2008 - 10:14 AM, said:

What you can do is when you write your report, or write a query, list the first name before the last name. Or you can do something like this
Select ([Table]![FIRST_NME]) & ", " & ([Table]![LAST_NME]) as Name
From Table
Where x=y



To my knowledge, the only other way to do what you want is to have the column names in the exact order you want them.


from my understanding, the firstname and lastname are in the same column. so one column would have "Doe, John" as it's value.

so he would need to do this:

-- if no space after the comma
SELECT SUBSTRING(column_Name, CHARINDEX(',', column_Name) + 1, LEN(column_Name) - CHARINDEX(',', column_Name) + 1)
		+ ' ' + SUBSTRING(column_Name, 1, CHARINDEX(',', column_Name) - 1)
FROM TableName 


-- if there is a space after the comma
SELECT SUBSTRING(column_Name, CHARINDEX(',', column_Name) + 2, LEN(column_Name) - CHARINDEX(',', column_Name) + 1)
		+ ' ' + SUBSTRING(column_Name, 1, CHARINDEX(',', column_Name) - 1)
FROM TableName


This post has been edited by eclipsed4utoo: 01 December 2008 - 12:04 PM

Was This Post Helpful? 1

#7 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: SQL - last name, first name reversal

Posted 01 December 2008 - 08:53 PM

By the way, you didn't mention what database you're using. The string manipulation functions and operators available to you can and will vary with the product you're using. In particular, the use of & and + for string concatenation in the above posts are not in the ANSI standard and are not supported by all database servers (for example, PostgreSQL and MySQL will choke on those). So just be aware that you may need to adjust these solutions to your environment.
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: SQL - last name, first name reversal

Posted 01 December 2008 - 09:52 PM

View PostAdaHacker, on 1 Dec, 2008 - 10:53 PM, said:

By the way, you didn't mention what database you're using. The string manipulation functions and operators available to you can and will vary with the product you're using. In particular, the use of & and + for string concatenation in the above posts are not in the ANSI standard and are not supported by all database servers (for example, PostgreSQL and MySQL will choke on those). So just be aware that you may need to adjust these solutions to your environment.


agreed. My example works with SQL Server.
Was This Post Helpful? 0
  • +
  • -

#9 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: SQL - last name, first name reversal

Posted 02 December 2008 - 07:54 AM

You should store the first name and last name values in separate fields.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1