Remove characters from Query result

Trying to remove characters from query result.   In following file; "68 Widewater Front 2_150x0.jpg", which is a thumbnail image.  

I am trying to get the query to trim the  "_150x0" characters from the result so I can display the thumbnail, rather than the full image.

Query returns  "68 Widewater Front 2.jpg", which is the file name for the full image in the database.

Below is how I have set up query, but it's not working.

SELECT users.user_pic
FROM users
TRIM _150x0
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
SELECT CONCAT(SUBSTRING_INDEX(users.user_pic,"_",1), SUBSTRING_INDEX(users.user_pic,"x0",-1)) FROM users
Cornelia YoderArtistCommented:
Or if it's always a  .jpg  you can use

SELECT CONCAT(SUBSTRING_INDEX(users.user_pic,"_",1), ".jpg") FROM users
scbasserAuthor Commented:
I modified the SQL statement to allow for filenames with more than 1 underscore.   When I test itin Dreamweaver Recordset TEST, it works perfectly, but when I use the page in ColdFusion, I keep getting following error.

Variable SUBSTRING_INDEX is undefined
Here is my Query:
<cfquery name="rsAGListDetailUserPic" datasource="dsmyretv">
SELECT user_id, user_co, CONCAT(SUBSTRING_INDEX(users.user_pic,"_50x0",1)) , user_pic
FROM users
WHERE user_id = <cfqueryparam value="#URL.user_id#" cfsqltype="cf_sql_numeric">
Here is the code that throws the error:
The error occurred in C:\ColdFusion8\wwwroot\CFmyRETV\listings\AgentListDetail.cfm: line 78
76 : <table width="549" border="0" bgcolor="#CCCCCC" id="Agent">
77 :               <tr>
78 :                 <td width="80" rowspan="4"><img src="<cfoutput>../FILES/Region_1/USER_#rsAGListDetailUserPic.user_id#/thumbnails/#rsAGListDetailUserPic.CONCAT(SUBSTRING_INDEX(users.user_pic,"_50x0",1))# </cfoutput>" width="75" /></td>
79 :                 <td colspan="2" nowrap="nowrap"><span class="style1"> </span><span class="style1"></span></td>
80 :                 <td width="5"></td>

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

what about just processing it on the output?

<img src='#replaceNoCase(myquery.user_pic, "_150x0", "", "ALL")#'>

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cornelia YoderArtistCommented:
SUBSTRING_INDEX came in a recent release of MySQL, so if you are using an old version, that might be the problem with it.
scbasserAuthor Commented:
Your solution worked well, although I have been using the Show Dynamic Thumbnail server behavior available in the Developers Toolbox.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.