We help IT Professionals succeed at work.

Remove characters from Query result

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

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

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

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

Author

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

Commented:
what about just processing it on the output?


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

Open in new window

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

Author

Commented:
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.