Solved

Adding Extra Characters to query output

Posted on 2011-03-20
4
296 Views
Last Modified: 2012-06-27
Hey guys,

I would like to find out how to add text to the starting and/or the end of a column output.....


query example --
SELECT price, category, item_image
FROM table_name

before --
9.99, electronic_item, item_image.jpg

after --
9.99, electronic_item, item_image_90.jpg

What I'm looking for is to have for e.g "_90" suffixed or put in behind of all image records that are produced from the query results..
0
Comment
Question by:Eaddy Barnes
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:Amick
Amick earned 250 total points
ID: 35177283
SELECT price, category, item_image||'_90'
FROM table_name
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 100 total points
ID: 35177297
The end of a string is easy. Select image_name+'_90' from table
The harder part is to insert it before the .jpg. Using left and search it can be done. You can also do replace to replace .jpg with _90.jpg
0
 
LVL 2

Accepted Solution

by:
michael_madsen earned 150 total points
ID: 35177300
This query:

SELECT price, category, item_image+'_90'
FROM table_name

will add '_90' to all values in [item_image] field, but if you have the field values to be: 'item_image.jpg', the result would be: 'item_image.jpg_90', which isn't what you want.

What you could do is:

SELECT price, category, replace(item_image, '.jpg', '_90.jpg')
FROM table_name
0
 
LVL 12

Assisted Solution

by:Amick
Amick earned 250 total points
ID: 35177323
This is the MySQL way to do it:

SELECT price, category, concat(item_image,'_90')
FROM table_name 

Open in new window


The SQL Server / Microsoft Access way
SELECT price, category, item_image+'_90'
FROM table_name 

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question