Query sql to search for and a string equal to but larger than the one stored in the table

Asked

Viewed 205 times

0

I have a table that stores screen urls from my system, I wanted to perform a query that returns me a url that comes parameterized as follows /avaliacoes-cozinha-segura/avalia-cozinha/2 to that url that is stored so /avaliacoes-cozinha-segura/avalia-cozinha/, I tried to use the like but it didn’t work out too well:

SELECT * FROM fogasdigital.TelaPerfil where url like ('%/avaliacoes-cozinha-segura/avalia-cozinha/2%');

because apparently the like would only work if the url was smaller than the one that is stored, but unfortunately I have no way to bring it reduced from the backend, I also tried to use the in but also did not succeed. What’s impatient is this 2 that comes from the system.

sql database query mysqli

  • The URL is recorded as /evaluations-kitchen-safe/evaluates-kitchen/ and you need to return it as /evaluations-kitchen-safe/evaluates-kitchen/2? The two in this story, is not present in the database, you just need for the return?

  • The url /avaliacoes-cozinha-segura/avalia-cozinha/ is the one in the bank and the url /avaliacoes-cozinha-segura/avalia-cozinha/2 is the search parameter SELECT * FROM fogasdigital.TelaPerfil where url like ('%/avaliacoes-cozinha-segura/avalia-cozinha/2%');

1 answer

0

You have some ways to normalize your string for comparison.

The first would be in SQL itself using a kind of Lastindexof in T-SQL:

REVERSE(SUBSTRING(REVERSE('/avaliacoes-cozinha-segura/avalia-cozinha/2'),CHARINDEX('/',REVERSE('/avaliacoes-cozinha-segura/avalia-cozinha/2')),len('/avaliacoes-cozinha-segura/avalia-cozinha/2')))

The second would be in your application or TSQL as well, removing the last char with the substring:

SUBSTRING('/avaliacoes-cozinha-segura/avalia-cozinha/2', 1, LEN('/avaliacoes-cozinha-segura/avalia-cozinha/2') - 1)

But I think you will probably lose performance comparing strings on your system, maybe it would be cool if you rethink your code.

Browser other questions tagged sql database query mysqli

You are not signed in. Login or sign up in order to post.