asked
BETWEEN QUERY
Hi, I've two tables namely Mobile & Mobile Range. CREATE TABLE MOBILE (MOB_NUM VARCHAR(20)) GO INSERT INTO MOBILE VALUES ('9993339971') go INSERT INTO MOBILE VALUES ('9993339972') go INSERT INTO MOBILE VALUES ('9993339973') go INSERT INTO MOBILE VALUES ('9994339939') GO CREATE TABLE MOBILE_RANGE (MOB_FROM VARCHAR(20),MOB_TO VARCHAR(20)) GO INSERT INTO MOBILE_RANGE VALUES ('9994339910','9994339920') go INSERT INTO MOBILE_RANGE VALUES ('9994339921','9994339930') go INSERT INTO MOBILE_RANGE VALUES ('9994339931','9994339950') go INSERT INTO MOBILE_RANGE VALUES ('9994339961','9994339970') GO Actually i want mask (999XXX9971) few digits in MOBILE table. But if any mobile available in MOBILE_RANGE, then i should not mask it. Example: 9994339939 available in MOBILE & also between 9994339931 - 9994339950 in MOBILE_RANGE. So i dont want to mask the 9994339939. I need to write query for this scenario. Help me Regards, karthik
If I understand you correctly, I think you want a cross join. This will match every row in one table up with every row in the other table. Then you can do a BETWEEN to get a list of MOB_NUM that you don't want to filter out: UPDATE MOBILE SET MOB_NUM = LEFT(MOB_NUM, 3) + 'XXX' + RIGHT(MOB_NUM, 4) WHERE MOB_NUM NOT IN ( SELECT DISTINCT MOB_NUM FROM MOBILE M CROSS JOIN MOBILE_RANGE R WHERE M.MOB_NUM BETWEEN MOB_FROM AND MOB_TO )
21 People are following this question.
