CODEWITHSUNDEEP
sqlsqlite
SamIAm
SamIAm

Reputation: 2491

Calling SQL Substr Function after the Where Clause is used

I have a SQL query as follows:

select telephone from tableA where telephone !="";

now when the above would work as it would do the command properly. However, as in my area telephones are required to be 10 digits long and sometimes the data is missing the leading '0', I have changed the sqlQuery to as follows:

select substr('0000000000'||telephone,-10,10) from tableA where telephone != "";

The above query would not work with the "where" clause as all empty fields would now contain '0000000000'.

Is there a way to check first if the telephone is empty and then do the above substr function?

The above example is just a simple one so I can just change where telephone != '0000000000' but I have other more complex queries that I would like to use and I do not want to change the where clause.

Upvotes: 0

Views: 94

Answers (1)

paxdiablo
paxdiablo

Reputation: 881523

The above query would not work with the "where" clause as all empty fields would now contain '0000000000'.

No, not so. The fact that you have where telephone != "" in your query means that rows with empty fields are not actually included in the results.

The select will only extract rows that meet the conditions specified in the where clause, then it will apply any transformations to the rows that have been extracted.

Is there a way to check first if the telephone is empty and then do the above substr function?

Yes, there is. You're doing it. Problem solved :-)

Upvotes: 1

Related Questions