0

I am trying to SELECT from a table, but I need to trim from leading and trailing string for comparison. For example,

My table/data is very simple

CREATE TABLE `lcr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prefix` varchar(45) DEFAULT NULL,
  `digits` varchar(45) DEFAULT NULL,
  `price` decimal(20,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

mysql> select * from lcr;
+----+--------+--------+--------+
| id | prefix | digits | price  |
+----+--------+--------+--------+
|  1 | 1122   | 92300  | 0.0198 |
+----+--------+--------+--------+
1 row in set (0.00 sec)

I am running the following select

SELECT * FROM `lcr` WHERE `digits` IN ((SELECT (TRIM(LEADING '1122' FROM '112292300'))), (SELECT (TRIM(LEADING '1122' FROM '11229230'))), (SELECT (TRIM(LEADING '1122' FROM '1122923'))), (SELECT (TRIM(LEADING '1122' FROM '112292'))))

which trims the leading 1122 from digits, and gives a positive match. But when I run the query

SELECT * FROM `lcr` WHERE `digits` IN ((SELECT (TRIM(LEADING '1122' FROM '92300'))), (SELECT (TRIM(LEADING '1122' FROM '9230'))), (SELECT (TRIM(LEADING '1122' FROM '923'))), (SELECT (TRIM(LEADING '1122' FROM '92'))))

I also get a positive match. Because Trim is actually not doing anything and in my table, digits are 92300.

Is there a way we can use some sort of condition or if function or something like that which should only return the result if prefix+trim = actual data passed?

I may not be much expressive asking my question here, so apologize for that and let me know if there is any info required to answer my question.

Thanks.

  • You mean WHERE CONCAT(prefix,digits) IN ('112292300', '11229230', ..., '112292') – ypercubeᵀᴹ Nov 19 '13 at 18:27
  • Not really a CONCAT because the input I am getting from the user is like '11229230012345' so I have no way of knowing what is the prefix out of the actual digits (as per the table). That's why I am running IN with 1 digit less each time so I get the least match. If I have another row which has same prefix, 1122 but digits are different, there will be a match as well because I am removing 1 digit at a time to use IN – NBhatti Nov 19 '13 at 18:32
  • Have you considered using LIKE? For example, SELECT * FROM lcr WHERE digits LIKE '1122%'? – ESG Nov 20 '13 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.