Query to find values based on a like and a not like

Question

I have a column in a table: ID - this column can have a value of '123456' but can also have value '123456x'.

I need to return all the numbers where there is an 'x' and the same number but without an 'x' doesn't exist. So if '123456' and '123456x' both exist I don't get a result. If '345678' exists and '345678x' does not exist then I get a result.

I'm sure this must be simple using a subquery and likes but it's wrecking my brain trying to construct the query. Please help!

Thanks

Answer 1

You didn't specify which type of database you are using. Here's a solution that works in Oracle and SQL Server (2022)

SELECT TRIM(TRAILING 'x' FROM ID) as ID   
FROM table1
GROUP BY TRIM(TRAILING 'x' FROM ID)
HAVING COUNT(TRIM(TRAILING 'x' FROM ID)) <> 2

Or if you don't like the duplicate TRIM() statements:

SELECT id
FROM 
(
    SELECT TRIM(TRAILING 'x' FROM ID) AS id
    FROM table1
)
GROUP BY id
HAVING COUNT(id) <> 2

Answer 2

In SQL Server, you may achieve this using sub query and aggregate functions:

SELECT S.CleanNumber
FROM (
    SELECT Q.CleanNumber,
           COUNT(Q.IsNumber) AS NumCount
    FROM (
        SELECT REPLACE(NumValue, 'x', '') AS CleanNumber,
               ISNUMERIC(NumValue) AS IsNumber
        FROM TestNumbers
        ) Q
    GROUP BY Q.CleanNumber
    ) S
WHERE S.NumCount = 1

Working SQL Fiddle with schema and sample data: https://dbfiddle.uk/62o-fRWc

Answer 3

Since it's unclear if you want to keep rows where the same exact value appears twice (e.g. two different rows with the same exact ID of 345678), and the other answers don't keep those rows, I figured I'd give you an answer that does. (If I had to guess, ID is unique and it's a moot point, but 🤷‍♂️.)

You can accomplish this with a simple outer join to itself like so:

SELECT T1.ID
FROM YourTable AS T1
LEFT JOIN YourTable AS T2
    ON CONCAT(T1.ID, 'X') = T2.ID
    OR CONCAT(T2.ID, 'X') = T1.ID
WHERE T2.ID IS NULL;

No need for subqueries, etc. This basically just says: return only the rows where there's no match on an ID that ends with an 'X' to the same ID that doesn't end with one.