We help IT Professionals succeed at work.

Remove characters from Query result

scbasser asked
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
Watch Question

SELECT CONCAT(SUBSTRING_INDEX(users.user_pic,"_",1), SUBSTRING_INDEX(users.user_pic,"x0",-1)) FROM users

Or if it's always a  .jpg  you can use

SELECT CONCAT(SUBSTRING_INDEX(users.user_pic,"_",1), ".jpg") FROM users


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

what about just processing it on the output?

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

Open in new window

SUBSTRING_INDEX came in a recent release of MySQL, so if you are using an old version, that might be the problem with it.


Your solution worked well, although I have been using the Show Dynamic Thumbnail server behavior available in the Developers Toolbox.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.