LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > General
User Name
Password
General This forum is for non-technical general discussion which can include both Linux and non-Linux topics. Have fun!

Notices


Reply
  Search this Thread
Old 03-25-2018, 08:56 PM   #1
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,443

Rep: Reputation: 253Reputation: 253Reputation: 253
Smile Mysql table data model


Hello guys, I am sure there's quite a few here that host DBs in Linux with MySQL and quite savvy as well with SQL.

Just need your opinion guys.

Select concat is a good command to combine strings or data.

But I notice it's works good if the data or string is in a single line or in one row.

What I mean DB is like this:

Table1
id First_name Last_name
1 John Dough
---------------------
2 Thriving Hardy
----------------------

So select concat works best in combining data of the two columns since everything is in a single row or line.

But what if there is multiple lines in a row, how to combine two columns as a single row?

Example:
Table1 (same as above but with another line)
id First_name Last_name
1 John Dough
Nickname JDH
----------------------
2 Thriving Hardy
Nickname THY


Desired output:

Combine_xresult
John-Dough
Nickname-JDH
------------
Thriving-Hardy
Nickname-THY

How to do in SQL the desired output above?

Thanks for any ideas.
 
Old 03-26-2018, 02:05 AM   #2
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 4,927

Rep: Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865
I don't understand your question. You say "There are multiple lines in a row". What do you mean by that? Is there a line feed?

Quote:
Originally Posted by JJJCR
id First_name Last_name
1 John Dough
Nickname JDH
Are these 2 rows? How? The column definition is 3 columns: "id First_Name Last_Name". How can you get the second row? Or does the second row have no ID and is the First_Name=Nickname?

jlinkels
 
Old 03-26-2018, 03:14 AM   #3
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,443

Original Poster
Rep: Reputation: 253Reputation: 253Reputation: 253
Hi jlinkels, it's just one row but two lines.

the id is just there for illustration, i only need to combine the first_name and last_name which has multiple lines.
 
Old 03-26-2018, 04:44 AM   #4
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 4,927

Rep: Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865
I really don't understand. How can one row have two lines. You have 3 columns. In which colums does Nickname JDH occur.

Can you post verbatim (copy paste) the output from the SELECT statment(s) you want to you to get the output?

If it is just output formatting, insert a newline in the concat statement.
Code:
mysql> SELECT CONCAT("My", "\n", "Name");
+----------------------------+
| CONCAT("My", "\n", "Name") |
+----------------------------+
| My
Name                    |
+----------------------------+
jlinkels
 
Old 03-27-2018, 09:48 PM   #5
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,443

Original Poster
Rep: Reputation: 253Reputation: 253Reputation: 253
The data on the cell is multi line string.

I will just redesign the data model, I will not use multi string. I think it can be done but I will need to re-process the string with loops and combine it.

Thanks.
 
Old 03-28-2018, 07:05 AM   #6
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 4,927

Rep: Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865
You could delete the newline with the REPLACE function:
Code:
SELECT(REPLACE("Hello \n World", "\n", ""));
This will print "Hello World" without newline in between.

But it is fairly inelegant. You have to use the REPLACE statement to delete the newline in your SELECT statement, then perform a CONCAT and insert the newline again. In addition, the Nickname actually appears in the Last_name column,

I agree with you it is better to rethink your table design. Keep that as clean as possible, no formatting. Only when you retrieve the data, apply formatting as needed.

jlinkels
 
Old 04-13-2018, 01:12 AM   #7
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,443

Original Poster
Rep: Reputation: 253Reputation: 253Reputation: 253
Thanks Jlinkels, yes i resorted to creating up a new table with a pre-formatted output. The moment I export the data it is already formatted to the way I want it to be.
But I think there's still a way to do it, just can't figure it out.
 
Old 04-13-2018, 07:41 PM   #8
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,078

Rep: Reputation: 675Reputation: 675Reputation: 675Reputation: 675Reputation: 675Reputation: 675
Why not add a third column for nickname?
Code:
SELECT id, First_name, Last_name, Nickname FROM Table1;

id First_name Last_name Nickname
 1 John       Dough     JDH
 2 Thriving   Hardy     THY

SELECT id,
       CONCAT_WS(
          "\n", 
          CONCAT_WS('-', First_name, Last_name),
          CONCAT('Nickname-', Nickname)
       ) AS Name_Text
FROM Table1;

id Name_Text
 1 John-Dough
Nickname-JDH

 2 Thriving-Hardy
Nickname-THY
Edit, did not read the problem carefully...
So Last_name is multiline?

Code:
SELECT CONCAT_WS('-', First_name, REPLACE(Last_name, ' ', '-')) AS Name_Text
FROM Table1;

Last edited by keefaz; 04-13-2018 at 08:01 PM.
 
Old 04-19-2018, 07:42 PM   #9
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,488

Rep: Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408Reputation: 2408
I'm definitely with Jlinkels (having variously worked with Oracle/Sybase/mysql/postgres/sqlserver) over many years.

ALWAYS keep the data model (& therefore table defns) lean, clean & consistent.
Any 'funny' formatting should always be handled by the app code.
It's also more flexible for the future.

Under some(!) circumstances, it may make sense to create Views with more complex formatting built in for use by Presentation layer (GUI), but never the underlying tables (imho).
 
Old 04-22-2018, 06:18 PM   #10
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 9,075
Blog Entries: 4

Rep: Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163Reputation: 3163
This might be a situation that is best handled by your application. Simply let it retrieve the values in each of the columns, without asking MySQL to concatenate them for you, and then combine them yourself stripping out newline characters from the data if needed. Queries are efficient at selecting and retrieving data but they do not supplant program logic (or clever spreadsheet formulas).
 
  


Reply

Tags
sql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer data in one of mysql table propertyagent Programming 5 06-22-2012 02:32 PM
Removing Old Table Data From MySQL carlosinfl Linux - Server 1 07-06-2009 10:01 AM
why do i get this error when inserting data to a mysql table verbatim Programming 2 06-15-2005 06:12 AM
mysql - transfer data from one table to another?? macinslaw Programming 22 08-20-2004 01:29 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > General

All times are GMT -5. The time now is 01:07 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration