Menu
I'm trying to retrieve all columns that start with any non alpha characters in SQlite but can't seem to get it working. I've currently got this code, but it returns every row:
SELECT * FROM TestTable WHERE TestNames NOT LIKE '[A-z]%'
Is there a way to retrieve all rows where the first character of TestNames are not part of the alphabet?
367
In SQL Server, we can use NOT LIKE with a list of alphanumeric characters that we want to exclude from the result: SELECT c1 FROM t1 WHERE c1 NOT LIKE '%[a-zA-Z0-9]%'; That returns rows that only contain non-alphanumeric characters.
To delete the first characters from the field we will use the following query: Syntax: SELECT SUBSTRING(string, 2, length(string));
You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column. SELECT LEN(column_name) FROM table_name; And you can use SUBSTRING or SUBSTR() function go get first three characters of a column. Save this answer.
The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes (for example, WHERE last_name='Vader' ).
Are you going first character only?
select * from TestTable WHERE substr(TestNames,1) NOT LIKE '%[^a-zA-Z]%'
The substr function (can also be called as left() in some SQL languages) will help isolate the first char in the string for you.
edit: Maybe substr(TestNames,1,1) in sqllite, I don't have a ready instance to test the syntax there on.
Added:
select * from TestTable WHERE Upper(substr(TestNames,1,1)) NOT in ('A','B','C','D','E',....)
Doesn't seem optimal, but functionally will work. Unsure what char commands there are to do a range of letters in SQLlite.
I used 'upper' to make it so you don't need to do lower case letters in the not in statement...kinda hope SQLlite knows what that is.
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
