How to add a new column by filtering the existing column using multiple conditions with subquery?

Question

I am working on a table in sql to add a new column using existing column of phone number and filter our the valid and invalid phone numbers n the new column.
Here are the conditions-
1. Phone number should 10 digit and should start with 8 or 9.
2. Phone number should 11 digit and its first two digits should be 0 and 9.
3. Phone number should 12 digit and its first two digits should be 9 and 1.
4. Phone number should not start with any alphabet.
5. If the phone number abides by any one of the above given conditions then it should show up in the new column else it should show up as invalid or Null.

Here is something that i have tried but not working:

Select *, ROW_NUMBER() over (partition by [Phone No] order by [Customer No] DESC) as RowNumber,
(Select(ltrim(rtrim([Phone No])) like '[^a-z]%' and 
((len(ltrim(rtrim([Phone No]))) = 10 and [Phone No] like '[89]%') or ([Phone No] like
case when (len(ltrim(rtrim([Phone No]))) = 11 and [Phone No] like '[0][9]%') then
[Phone No]
end) or (len(ltrim(rtrim([Phone No]))) = 12 and [Phone No] like '[9][1]%'))) from 
[Data_Joined])
from [Data_Joined]

Please help! Thanks.

Check this answer - https://stackoverflow.com/a/31693412/1080354. Understand it and add RegexIsMatch function (which yields true for match and false for not matching a given regex expression). Then in your new column you will have: `IIF(dbo.RegexIsMatch('regex expression', [Phone No]) = 1, [Phone No], NULL)`. Let me know if you cannot write the function alone. — gotqn, Sep 09 '17 at 06:44

Anagha · Accepted Answer · 2017-09-09T16:14:12.673

I can not say it is efficient answer but still can fulfill your requirements.

select case when flg= 'V' then phone_no else null end as phone_no
from
(select phone_no ,'V' AS flg
from Data_Joined
where ltrim(rtrim(phone_no)) like '[^a-z]%'
and (phone_no like
case when len(ltrim(rtrim(phone_no))) = 12 and phone_no like '[9][1]%' then
phone_no end )
or (len(ltrim(rtrim(phone_no))) = 10 and phone_no like '[89]%') or (phone_no like
case when len(ltrim(rtrim(phone_no))) = 11 and phone_no like '[0][9]%' then
phone_no
end )
union
select phone_no,'I' AS flg from Data_Joined WHERE phone_no NOT in (select phone_no
from Data_Joined
where ltrim(rtrim(phone_no)) like '[^a-z]%'
and (phone_no like
case when len(ltrim(rtrim(phone_no))) = 12 and phone_no like '[9][1]%' then
phone_no end )
or (len(ltrim(rtrim(phone_no))) = 10 and phone_no like '[89]%') or (phone_no like
case when len(ltrim(rtrim(phone_no))) = 11 and phone_no like '[0][9]%' then
phone_no
end))) a

output:

phone_no
NULL
9029646510
919069745510
09039478311
NULL

score 0 · Answer 2 · answered Sep 11 '17 at 08:58

Thanks for the help! Here is the simplified version:

    Insert into [Data_Joined_1]
    Select *, case 
    when (len(ltrim(rtrim([phone_no]))) = 10 and 
    ltrim(rtrim([phone_no])) like '[89]%' and [phone_no] not like '%[a-
    z]%') then [phone_no]
    when (len(ltrim(rtrim([phone_no]))) = 11 and 
    ltrim(rtrim([phone_no])) like '[0][89]%' and [phone_no] not like '%[a-
     z]%') then right([phone_no],10)
    when (len(ltrim(rtrim([phone_no]))) = 12 and 
    ltrim(rtrim([phone_no])) like '[9][1]%' and [phone_no] not like '%
   [a-z]%') then right([phone_no],10)
    end as Filtered
    from [Data_Joined]

How to add a new column by filtering the existing column using multiple conditions with subquery?

2 Answers2