Remove first 11 characters from SQL query
Development and Programming
Anyone able to help me here? My head hurts! I’m querying my Matrix table fine, but need to remove the first 11 characters from my returned data. Obviously just want to remove the chars in my template, not my database. Been Google’ing for a while now with joy. Any help much appreciated!
{exp:query limit="12" paginate="bottom" sql="SELECT
row_id, entry_id, row_order, col_id_46, col_id_47, col_id_48
FROM exp_matrix_data
WHERE col_id_46 !=''
ORDER BY entry_id DESC, row_order ASC"
}
{col_id_46}
{/exp:query}
The SQL SUBSTRING command should be able to do that:
SELECT SUBSTR(col_id_46, 12), row_id, entry_id, row_order, col_id_47, col_id_48
FROM exp_matrix_data
WHERE col_id_46 !=''
ORDER BY entry_id DESC, row_order ASC"Untested.
Thanks Ingmar but doesn’t appear to be working. I’ll take a look at the SUBSTR feature though and see if I can figure it out. Thanks.
Yes, the SUBSTRING command is definitely the way to go. Give that a try.
If you simply want to effect the output and keep the SQl query intact you can use something like (if you PHP enabled for the template on the output stage:
<?php
echo substr("{col_id_46}", 3);
?>Sorry, made a typo since you want the first 11 you can use :
<?php
echo substr("{col_id_46}", 11);
?>But please note that that Ingmar’s solution is more effecient if you can correctly get the query mods going, this is just a variation that will uses more processing time.
Thanks everyone. I’m all sorted now using Ingmar’s approach.