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
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
SELECT CONCAT(SUBSTRING_INDEX(use rs.user_pi c,"_",1), SUBSTRING_INDEX(users.user _pic,"x0", -1)) FROM users
Or if it's always a .jpg you can use
SELECT CONCAT(SUBSTRING_INDEX(use rs.user_pi c,"_",1), ".jpg") FROM users
SELECT CONCAT(SUBSTRING_INDEX(use
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
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SUBSTRING_INDEX came in a recent release of MySQL, so if you are using an old version, that might be the problem with it.
ASKER
Your solution worked well, although I have been using the Show Dynamic Thumbnail server behavior available in the Developers Toolbox.