CODEWITHSUNDEEP
sql
HoverHell
HoverHell

Reputation: 4899

SQL startswith (using `LIKE`) on an expression

What's an appropriate way to do startswith(expression) in SQL?

I can do it with LIKE ((expression) || '%'), but it doesn't look very nice to me.

Full query is in form:

SELECT …, (SELECT COUNT(*) 
           FROM post AS child 
           WHERE child.path LIKE (post.path || '%') 
          AND child.depth >= post.depth)
FROM post WHERE …

I suppose it is preferable to use LIKE because of DB indexing for this case.

Upvotes: 28

Views: 140395

Answers (4)

J S
J S

Reputation: 899

WHERE CustomerName LIKE 'a%'
--Finds any values that start with "a"

WHERE CustomerName LIKE '%a'
--Finds any values that end with "a"

WHERE CustomerName LIKE '%or%'
--Finds any values that have "or" in any position

WHERE CustomerName LIKE '_r%'
--Finds any values that have "r" in the second position

WHERE CustomerName LIKE 'a__%'
--Finds any values that start with "a" and are at least 3 characters in length

WHERE ContactName LIKE 'a%o'
--Finds any values that start with "a" and ends with "o"

-- Case insensitive 2 SELECT * FROM my_table WHERE upper(my_column) LIKE 'SEARCHED %'; -- starts with

3 SELECT * FROM my_table WHERE upper(my_column) LIKE '% SEARCHED';
-- ends with

4 SELECT * FROM my_table WHERE upper(my_column) LIKE '%SEARCHED%'; -- contains

Upvotes: 5

Tom Anderson
Tom Anderson

Reputation: 47233

In standard SQL, you can also say:

where position(post.path in child.path) = 1

I don't know if your RDBMS supports that. PostgreSQL does.

Upvotes: 4

Antonio García
Antonio García

Reputation: 1

You can use

where DATE LIKE '[(SELECT STR(YEAR(GETDATE())-1))]%'

WHERE child.path LIKE '[(SELECT STR(YEAR(GETDATE())-1))]%' (post.path || '%')

Upvotes: 0

Scott Arrington
Scott Arrington

Reputation: 12503

Just use LIKE 'input%'. I.E:

WHERE child.path LIKE post.path + '%'

(I assume this is for SQL Server, though this syntax probably works elsewhere)

Upvotes: 39

Related Questions