CODEWITHSUNDEEP
sql
Chaya
Chaya

Reputation: 41

Verify if the second character is a letter in SQL

I want to put a condition in my query where I have a column that should contain second position as an alphabet.

How to achieve this?

I've tried with _[A-Z]% in where clause but is not working. I've also tried [A-Z]%.

Any inputs please?

Upvotes: 2

Views: 12477

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

The use of '%[A-Z]%' suggests that you are using SQL Server. If so, you can do this using LIKE:

where col like '_[A-Z]%'

For LIKE patterns, _ represents any character. If the first character needs to be a digit:

where col like '[0-9][A-Z]%'

EDIT:

The above doesn't work in DB2. Instead:

where substr(col, 2, 1) between 'A' and 'Z'

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

I think you want mysql query. like this

SELECT * FROM table WHERE column REGEXP '^.[A-Za-z]+$'

or sql server

select * from table  where  column like '_[a-zA-Z]%'

Upvotes: 5

Mihir Shah
Mihir Shah

Reputation: 988

agree with @Gordon Linoff, your ('_[A-Z]%') should work. if not work, kindly add some sample data with your question.

Declare @Table Table
(
    TextCol     Varchar(20)
)

Insert Into @Table(TextCol) Values
    ('23423cvxc43f')
,('2eD97S9')
,('sAgsdsf')
,('3Ss08008')

Select  *
From    @Table As t
Where   t.TextCol Like '_[A-Z]%'

Upvotes: 0

Oldskool
Oldskool

Reputation: 34867

You can use regular expression matching in your query. For example:

SELECT * FROM `test` WHERE `name` REGEXP '^.[a-zA-Z].*';

That would match the name column from the test table against a regex that verifies if the second character is either a lowercase or uppercase alphabet letter.

Also see this SQL Fiddle for an example of data it does and doesn't match.

Upvotes: 0

Related Questions