May 14, 2008 at 10:46 pm
Hi All!
Our company is starting to move into the idea of SMS'ing clients, in order to do this we need to have a set field [Mobile] with compatible phone numbers in this column.
Currently the structure of the table that we need to use have two Columns [Phone 1] & [Phone 2]
I need to copy contents of these two fields [Phone 1] & [Phone 2] into a new field [Mobile] within the same table. The issue is that BOTH [Phone 1] and [Phone 2] are mixed with different phone numbers - landlines and mobile numbers - the key identifier for a mobile number is that they begin with '04'
so my logic is that we would need something along the lines of:
SELECT ..
FROM [Members]
WHERE [Phone 1] LIKE N '04%' AND [Phone 2] LIKE N '04%' ... INSERT (*) INTO [mobile]
I know the syntax is all wrong as it should be a nested INSERT INTO - How can i reword this, as i know INSERT INTO needs the specific values and there are thousands of values making the whole process pointless.. Is there a way of doing this with another function - ie COPY..
Am i making sense..
Appreciate any help here!!
Thanks guys!! 😀
May 15, 2008 at 11:27 am
Can you give an example of phone1 and phone2 and the desired result?
Wild guesses:
update members
set [mobile]=phone1 + 'separator' + phone2
where ...
update members
set mobile=firstmatch
from members
inner join
(
select memberid,
CASE PHONE1 like ... THEN Phone1
CASE PHONE2 like ... Then Phone2
ELSE NULL
END AS Firstmatch
from members
where phone1 like ... and phone2 like ...
) preferredphone
on members.memberid=preferredphone.memberid
May 16, 2008 at 12:47 am
I had a similar experience with telephone number selection, using the “LIKE” statement didn’t work very well. I actually filled up all available disk space on the server because MSDB grew, needless to say I wasn’t the most popular guy in out company that day.
Also use the “OR” operator, because a mobile number may exist in Phone1 doesn’t mean it won’t in Phone2.
Try using “SUBSTRING”:
UPDATE
Members
SET
[mobile]=phone1 + 'separator' + phone2
WHERE
(SUBSTRING([Phone 1],2) = ‘04’ OR
SUBSTRING([Phone 2],2) = ‘04’ )AND
(Remaining Criteria)
Hope this helps.
Regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply