Concatenate In Sql

Need a field definition that is a combin

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 12935 Views - Last Post: 30 January 2005 - 11:40 AM

#1 ricirv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-January 05

Concatenate In Sql

Posted 20 January 2005 - 06:04 PM

I am using php/mysql and need to have a full name field which is a concatenation of firstname and surname. How do I define this in sql??
Is This A Good Question/Topic? 0
  • +

Replies To: Concatenate In Sql

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 20 January 2005 - 06:08 PM

SELECT CONCAT(FName,' ',LName) AS FullName FROM dbtable
Was This Post Helpful? 0
  • +
  • -

#3 ricirv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-January 05

Re: Concatenate In Sql

Posted 20 January 2005 - 06:20 PM

Is there any way I can do it when I create the table?
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 20 January 2005 - 06:22 PM

Do you need to create a table with a field named after two names, or insert a value that is actually two names?
Was This Post Helpful? 0
  • +
  • -

#5 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 20 January 2005 - 06:26 PM

I guess what we need to know is exaclty what your trying to do: Are you trying to insert a combination of two field values (fname, lname) into another field within that same record?
Was This Post Helpful? 0
  • +
  • -

#6 ricirv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-January 05

Re: Concatenate In Sql

Posted 20 January 2005 - 06:30 PM

yep, exactly what I need. Can I make the concatenation a default value or something?
Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 20 January 2005 - 06:46 PM

Well, I'm making the assumption that you will be creating the record by inserting all values at the same time. If you have a table in which the fields are fname,lname,fullname in that order, your insert statement should look something like
$strSQL = "INSERT INTO table VALUES ('$fname','$lname','$fname"." "."$lname')";


Alternately, you could set the variable outside the statement
$fullname = $fname." ".$lname;
$strSQL = "INSERT INTO table VALUES ('$fname','$lname','$fullname')";


Was This Post Helpful? 0
  • +
  • -

#8 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 20 January 2005 - 06:55 PM

Just as a question, is there a reason you need the names separately and together? If you have them separately, you can always pull them from the DB together.
Was This Post Helpful? 0
  • +
  • -

#9 ricirv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-January 05

Re: Concatenate In Sql

Posted 20 January 2005 - 07:37 PM

It is not much pulling them from the db for display purposes, it is more so I can have a menu/list for a html form that displays the full name but then picks up the record id for that person. I thought this was easier as I do not need the record id displayed on the scren anywhere.
Was This Post Helpful? 0
  • +
  • -

#10 ricirv  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-January 05

Re: Concatenate In Sql

Posted 20 January 2005 - 07:40 PM

What I really want to do is just have a form that has first name, surname but when they are inserted, the last name is done automatically. I will need a variable defined on the page that inserts the concatenated value of the 2 values in the form.
Was This Post Helpful? 0
  • +
  • -

#11 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 21 January 2005 - 06:04 AM

I may be misunderstanding, but the code I gave will insert the full name.
Was This Post Helpful? 0
  • +
  • -

#12 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: Concatenate In Sql

Post icon  Posted 30 January 2005 - 11:28 AM

Amadeus, on Jan 20 2005, 06:08 PM, said:

SELECT CONCAT(FName,' ',LName) AS FullName FROM dbtable

CONCAT takes only 2 Arguments...You have to nest the CONCAT Function to do that.

Otherwise you can use the || Concatenation Operator like this:
select FName || ' ' || LName as FullName from dbtable;

This post has been edited by born2c0de: 30 January 2005 - 11:29 AM

Was This Post Helpful? 0
  • +
  • -

#13 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 30 January 2005 - 11:32 AM

I hate to dispute, but if you'll run the code I posted in the command line of MySQL, you'll find it runs. I ran it in the MySQL Control Center Query Interface...it ran no worries.
Was This Post Helpful? 0
  • +
  • -

#14 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: Concatenate In Sql

Posted 30 January 2005 - 11:34 AM

oh...I should have mentioned....I type my SQL in SQL * Plus that Oracle Provides....the CONCAT Function takes 2 Arguments there....No Idea about MySQL.
Was This Post Helpful? 0
  • +
  • -

#15 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Concatenate In Sql

Posted 30 January 2005 - 11:36 AM

Then I agree, you are one hundred percent correct....the CONCAT function in SQL does not support the three arguments definition, I would never have mentioned it, but the original user specified mysql.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2