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.
4 Answers
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)
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.
You can use
where DATE LIKE '[(SELECT STR(YEAR(GETDATE())-1))]%'
WHERE child.path LIKE '[(SELECT STR(YEAR(GETDATE())-1))]%' (post.path || '%')
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
