update with concat
Hello, I have a phone column with numbers such as 9999999999. I'm trying to update the data to reflect
(999) 999-9999 ext. and allow up to a four character extension in the future. The datatype of the column is varchar (25).
I'm trying to use the code below but get this error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '('.
Here is my SQL:
update Technician
set [phone] = concat "(" + left(3) + ")" + " " + right((left,6),3) + "-" + right((left, 10),4)" + " " + "Ext." " + " " + right((left, 12),4)
where phone is not null
(999) 999-9999 ext. and allow up to a four character extension in the future. The datatype of the column is varchar (25).
I'm trying to use the code below but get this error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '('.
Here is my SQL:
update Technician
set [phone] = concat "(" + left(3) + ")" + " " + right((left,6),3) + "-" + right((left, 10),4)" + " " + "Ext." " + " " + right((left, 12),4)
where phone is not null
ASKER
Thanks AngelIII, that was fast!
I'm getting this error: The left function requires 2 arguments.
I'm getting this error: The left function requires 2 arguments.
you should use
left(yourfield, 3)
left(yourfield, 3)
Like this
update Technician
set [phone] = "(" + left(yourfield, 3) + ")" + " " + right(left(yourfield,6),3)
where phone is not null
update Technician
set [phone] = "(" + left(yourfield, 3) + ")" + " " + right(left(yourfield,6),3)
where phone is not null
I didn't check it but I guess you have a few " more than the ones needed
Please try this
update Technician
set [phone] = "(" + left(yourfield, 3) + ")" + " " + right(left(yourfield,6),3)
where phone is not null
Please try this
update Technician
set [phone] = "(" + left(yourfield, 3) + ")" + " " + right(left(yourfield,6),3)
where phone is not null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Btw, use single quotes (') not double (") for delimiting literals; " depends on specific settings to work correctly, while ' does not.
ASKER
Thanks Scott! That updated the column correctly. The other posts kept giving errors.
I will modify by table by adding an extension column and I can use a regular expression validator on the client side on the insert in the future. Thanks for the advice.
I will modify by table by adding an extension column and I can use a regular expression validator on the client side on the insert in the future. Thanks for the advice.
update Technician
set [phone] = "(" + left(3) + ")" + " " + right((left,6),3) + "-" + right((left, 10),4)" + " " + "Ext." " + " " + right((left, 12),4)
where phone is not null