Link to home
Start Free TrialLog in
Avatar of scbasser
scbasser

asked on

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
Avatar of Cornelia Yoder
Cornelia Yoder
Flag of United States of America image

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
Avatar of scbasser
scbasser

ASKER

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>
<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

ASKER CERTIFIED SOLUTION
Avatar of Kerrdo
Kerrdo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.