CODEWITHSUNDEEP
sqlregexoracle-database
Jeffy
Jeffy

Reputation: 131

sql start with and end with

I am trying to find the string start with D or K and end with 2 or 3 in Oracle SQL using regexp_like() function.

REGEXP_LIKE (Attribute, '(^D|^K)') shows

DL71
DR93
DW11
KL62
KT03
KV29

REGEXP_LIKE (Attribute, '(^D|^K)*') shows

AT94
BV06
CD52
DL71
DR93
DW11
FD21
KL62
KT03
KV29

REGEXP_LIKE (Attribute, '*(^2|^3)') shows

CD52
DR93
KL62
KT03

REGEXP_LIKE (Attribute, '(^D|^K)*(^2|^3)') shows

CD52 
DR93
KL62        
KT03

How should I correct it?

The data is as following:

Attribute
AT94
BV06
CD52
DL71
DR93
DW11
FD21
KL62
KT03
KV29

Upvotes: 3

Views: 14783

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you are looking for:

WHERE REGEXP_LIKE(Attribute, '^[DK].*[23]$')

You are looking for character classes. These are delimited by square braces.

The regular expression:

  • ^ at the beginning of the pattern means that the expression starts with the pattern.
  • [DK] is a character class matching exactly one occurrence of one of these characters.
  • .* matches any number of characters (newlines might be an issue).
  • [23] is a character class matching exactly one of these characters.
  • $ at the end of the pattern means that the expression ends with this pattern -- in this case, 2 or 3 at the end.

Upvotes: 6

MT0
MT0

Reputation: 168232

You don't need a regular expression; you can just use LIKE:

WHERE ( Attribute LIKE 'D%' OR Attribute LIKE 'K%' )
AND   ( Attribute LIKE '%2' OR Attribute LIKE '%3' )

Upvotes: 1

Related Questions