SQL name islands with only 5 characters-CodePudding

I have a database with Islands and their names. How can I get a list of the islands whose name contains only 5 characters without using the command length/LEN? I am looking for an alternative. Thanks :)

With Length it works but I am looking for an alternative method

CodePudding user response：

What about this?

SELECT * FROM table_name WHERE island_name LIKE '_____'

CodePudding user response：

You could use SUBSTRING(), if you are looking for an alternative method. You can check if a SUBSTRING of X characters == The Original String.

However, you would also need to account for 4-character strings, for example. You could add "padding characters", or you could make sure that X-1 Character Substring != X-character Substring. In Sql 2016 for example, these are the same with at least one case of query options:

SELECT SUBSTRING('ISLA',1,4)
SELECT SUBSTRING('ISLA',1,5)

CodePudding user response：

I agree that LENGTH is the best option. Maybe this is a school question.

Give this a try:

SELECT SUBSTR(field1,1,5) 
FROM table1 
WHERE substr(field1,5,1) IS NOT NULL 
    AND SUBSTR(segment1,6,999) IS NULL;;