How to get the values for which the format and suffix are known but the exact values are not known and there can be multiple values from the database?

Question

I have a use case as below:

I have thousands of records in the database and let's say I am having one column named myValue.

Now the myValue's actual value can be an alphanumeric string where the first two characters are alphabets, the next 6 characters are numbers and the last character is a fixed alphabet let say 'x', which may be or may not be present in the value. (For Example 'AB123456','AB123456x')

So I know the format of the value for myValue field but not know all the actual values as there are lots of records.

Now I want to retrieve all such values for which the value without last character x (For Example, 'AB123456') and the same value with last character x (For Example, 'AB123456x') exists.

So is there any way I can retrieve such data?

I am right now doing trial and error on existing data but have not found success and there are thousands of such rows, so any help on this would be appreciated a lot.

Answer 1

You can do so like this:

SELECT myvalue
FROM t
WHERE myvalue LIKE '________'
AND EXISTS (
    SELECT 1
    FROM t AS x
    WHERE x.myvalue = CONCAT(t.myvalue, 'x')
)

A (most likely) faster alternate is:

SELECT TRIM(TRAILING 'x' FROM myvalue) AS myvalue2
FROM t
GROUP BY myvalue2
HAVING COUNT(DISTINCT myvalue) > 1