0

I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City

The problem is,some forms come with incomplete data which is acceptable during the filling of the form which leaves me with data of the form names#Age and the rest of the data is not there.

Since there are many records i want first to process the data with all the four fields with data and i want to use regular expressions to select all those complete records first.

I have tried this query

SELECT text_message from incoming_sms where text_message REGEXP '^([a-z]+$)*[a-z]+$';

but this does not match what i want.

  • 1
    What about text_message LIKE '%#%#%#%'? – ypercubeᵀᴹ Oct 17 '14 at 12:56
  • I used this and it works SELECT text_message from incoming_sms where text_message REGEXP '^([^#\n]+#){3}[^\n#]+$'. Yours too works,i have tested it,finally.Could you add that as an answer?. Thanks. – user46420 Oct 22 '14 at 16:05
0

It depends on how strict you want to be, and on what you can expect from the form.

If all that's needed is the presence of 3 #'s, it would suffice to use

SELECT text_message
FROM incoming_sms
WHERE text_message LIKE '%#%#%#%';

If you want to have at least one character in each field, you can use (as you suggested yourself):

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#]+#){3}[^#]+$';

If you know that leading whitespace is trimmed from each field and you want to ensure you have at least one non-whitespace character, use:

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#\s]+[^#]*#){3}[^#\s]+[^#]*$';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy