0

from the below set of rows i need

  1. rows with 3 letters
  2. rows with special characters
  3. rows with combination of numbers and special characters,letters

from below records..

OJH,
WV],
2V,
W.W,
V,
@A,
AL,
AS,
1234,
1,
23

i need to select OJH,WV],2V,W.W,V,@A etc..

ie, combination of letters and special characters,combination of letters and numbers and letters or digits combination greater than 3

i need to skip AL,AS,1234,1,23 etc..

fusion3k
  • 11,568
  • 4
  • 25
  • 47
Aslam V Abdul Azees
  • 13
  • 2
  • 1
    what `regex` have you tried? – rock321987 Apr 01 '16 at 09:43
  • /\`|\~|\!|\@|\#|\$|\%|\^|\&|\*|\(|\)|\+|\=|\[|\{|\]|\}|\||\\|\'|\<|\,|\.|\>|\?|\/|\""|\;|\:|[a-zA-z0-9]{3}|[\s]|\s?\d+/ but it selects all digits like 123 1234 etc.. i want to select only combination of digits and letters like W2W, etc – Aslam V Abdul Azees Apr 01 '16 at 09:45
  • OJH, WV], 2V, W.W, V, @A, AL, AS, 1234, 1, 23 is a single string or an array with OJH in first record, WV] in 2nd record etc ? So, do you use a loop for check your regex ? – Xenofexs Apr 01 '16 at 09:49
  • an array with OJH in 1st WV] in 2nd etc.. – Aslam V Abdul Azees Apr 01 '16 at 09:51
  • 3
    *letters or digits combination greater than 3* - doesn't `1234` fit this group or does it have to contain both categories? – SamWhan Apr 01 '16 at 09:52
  • all these records are state values stored in db. AL,AS etc are alabama and alaska states of USA some people had typed ALA or AL] or A*A or A2A for Alaska.So i need to get those values greater than two. Also some states of countries are in digits,say 1234 = New Delhi ..so i need to skip that 1234.. but no state value will be having both letter and digit.. like W2W or V2 .. – Aslam V Abdul Azees Apr 01 '16 at 09:57
  • I'm still confused - what criteria passes `OJH`? What filters `1234` out? – SamWhan Apr 01 '16 at 10:04
  • OH is mistyped as OJH for OHIO-USA ,so i need OJH but 1234 or 123 or 12 might be some state of some country... but no state will be having a value of combination of letters,digits and special characters.. like O] W2 W.W W2W etc.. here in my db states of USA have 2 letter alphabets and other country states have digit values but not both – Aslam V Abdul Azees Apr 01 '16 at 10:06
  • So what you're simply saying that *keep everything that isn't a number or 2 letters*? – SamWhan Apr 01 '16 at 10:12
  • i need to get 1) all the records that has letters > 2 ie, OJH .2) all the records with special characters and numbers A@A,A&,A],W2W.. no need to select just numbers .. ie, 1234 123 2 1 etc – Aslam V Abdul Azees Apr 01 '16 at 10:17
  • In that case `V` shouldn't be valid, correct? – anubhava Apr 01 '16 at 10:43

1 Answers1

0

If I understand you correctly, this should do it:

^(?=.*[A-Z])(?=.*\d).*$|^(?=.*[@\].])(?=.*\d).*$|^(?=.*[@\].])(?=.*[A-Z]).*$

Using positive look-ahead it checks for lines containing

  • both letters and digits
  • both special characters and digits (in this case @, ] and . counts as special)
  • both special characters and letters

Check this example at regex101.

This assumes the combinations are tested one by one - no commas.

Regards.

Edit

Missed the wrong number of letters option. This should do it:

^(?=.*[A-Z])(?=.*\d).*$|^(?=.*[@\].])(?=.*\d).*$|^(?=.*[@\].])(?=.*[A-Z]).*$|^[A-Z]$|^[A-Z]{3,}$

See it here.

SamWhan
  • 8,296
  • 1
  • 18
  • 45
  • it works but didnt selected values like wv' , v etc ie, words having single quotes and single letter state values which may or may not be followed by a space ie, V or spaceV – Aslam V Abdul Azees Apr 01 '16 at 11:04
  • it works but didnt selected values like wv' ,Y','b etc.. how can it be fixed ? – Aslam V Abdul Azees Apr 01 '16 at 11:11
  • The simplest way, if your definition of *special characters* can be stretched to match *any character* **but** *digits, letters (A-Z, a-z)* **and** `_`, then you could change the RE to `^(?=.*[A-Z])(?=.*\d).*$|^(?=.*\W)(?=.*\d).*$|^(?=.*\W)(?=.*[A-Z]).*$|^[A-Z]$|^[A-Z]{3,}$`. The `\W` is the opposite of `\w` which matches digits, A-Z - both cases - and `_`. – SamWhan Apr 01 '16 at 11:28
  • Otherwise you'll have to insert all characters into the character sets defined in the regex. It's defined by the `[@\].]` part which means `@`, `]` and `.`. Add any signs you consider *special* inside the enclosing brackets. Note! The *closing bracket* has to be escaped (the `\` before the first `]`). Don't break that by placing characters between them. – SamWhan Apr 01 '16 at 11:28