CODEWITHSUNDEEP
javasql-serverhibernatesql-like
AdamN
AdamN

Reputation: 1

SQL like query contains only a specific character

I'm trying to get records from SQL server starting with abc_ followed by at least one letter (a-z, A-Z) and then continue to the end with just letters and numbers (a-z, A-Z, 0-9). No special characters like . , ; _ ? -

I am using javax.persistence.Query to get result from dao so i prefer use some like as condition. But mostly I am tring to find paternt to SQL query. I think thats not regex language

Java implementation

public List<UserExport> getUserExport() {
        Query query = userExportDao.createQuery("SELECT c FROM UserExport c WHERE c.objectId LIKE :value").setParameter("value", "abc_[a-z,A-Z]%");
        return query.getResultList();
    }

objectId is varchar(255), primary key, not null

Match

abc_KlimD
ABC_PackoL
abc_novo65ny
abc_abcd
abc_abc1

Do not match

abc_ola_old
aBC_12615
abc_6novotny
abc_abc_
abc_ab_d

SELECT @@version
Microsoft SQL Server 2022

Upvotes: 0

Views: 98

Answers (2)

AdamN
AdamN

Reputation: 1

I found a solution for my problem, but it's more complicated than I expected

The REGEXP operation is not supported on Microsoft SQL Server 2022 and LIKE alone is not strong enough.

Use LIKE and NOT LIKE

SELECT count(*)
FROM USER_EXPORT
WHERE objectId LIKE 'abc_[A-Za-z]%' 
AND objectId NOT LIKE 'abc_[A-Za-z]%[^A-Za-z0-9]%';

Use LIKE and PATINDEX

SELECT count(*)
FROM USER_EXPORT
WHERE objectId LIKE 'abc_[A-Za-z]%'
AND PATINDEX('%[^A-Za-z0-9]%', SUBSTRING(objectId, 5, LEN(objectId))) = 0;

SELECT count(*)
FROM USER_EXPORT
WHERE objectId LIKE 'abc_[A-Za-z]%'
AND PATINDEX('%[^A-Za-z0-9]%', RIGHT(objectId, LEN(objectId) - 4)) = 0;

SELECT count(*)
FROM USER_EXPORT
WHERE objectId LIKE 'abc_[A-Za-z]%'
AND PATINDEX('%[^A-Za-z0-9]%', STUFF(objectId, 1, 4, '')) = 0;

Solution in Java

public List<UserExport> getUserExport() {
        Query query = userExportDao.createQuery("SELECT c FROM UserExport c WHERE c.objectId LIKE :value1 AND objectId NOT LIKE :value2")
                .setParameter("value1", "abc_[a-z,A-Z]%")
                .setParameter("value2", "abc_[A-Za-z]%[^A-Za-z0-9]%");

        return query.getResultList();
    }

Upvotes: 0

shawnt00
shawnt00

Reputation: 17935

This should match all the requirements as a singular affirmative test:

c.objectId like 'abc[_][a-zA-Z]' + replicate('[a-zA-Z0-9]', len(c.objectId) - 5)

If you don't like the variable-length pattern created on the fly then maybe match on the prefix and then reject the ones with bad characters after the initial five characters:

c.objectId like 'abc[_][a-zA-Z]%' and c.objectId not like '_____%[^a-zA-Z0-9]%'

https://dbfiddle.uk/NalRA0rO

Upvotes: 1

Related Questions