December 16, 2002 at 2:07 pm
I have another question that is stumping me. I have a database SQL 2000, that has telephone #'s in one of the fields. The format is currently 3333333333. I would like the format to be (XXX-XXX-XXXX) in the database. I wrote this query ;
UPDATE Members SET Members.WORKPHONE = IIf(Len([WORKPHONE])= 10,"(" &
Left([WORKPHONE],3) & ") " & Mid([WORKPHONE],4,3) & "-" &
Mid([WORKPHONE],7),[WORKPHONE]);
i put that in the query analyzer and i get this error message "Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='."
Does anyone see this error. I can't..
Thanks to anyone for help.
December 16, 2002 at 2:51 pm
I don't believe that is TSQL Syntax. Try this in QA.
DECLARE @WORKPHONE AS NVARCHAR(12)
SELECT @WORKPHONE = '1234567890'
SELECT CASE LEN(@WORKPHONE)
WHEN 10 THEN
'(' + Left(@WORKPHONE,3) + ') ' +
substring(@WORKPHONE,4,3) + '-' +
substring(@WORKPHONE,7,4)
ELSE @WORKPHONE
END
SELECT @WORKPHONE = '123456789'
SELECT CASE LEN(@WORKPHONE)
WHEN 10 THEN
'(' + Left(@WORKPHONE,3) + ') ' +
substring(@WORKPHONE,4,3) + '-' +
substring(@WORKPHONE,7,4)
ELSE @WORKPHONE
END
December 16, 2002 at 3:04 pm
Hey Tad thanks man. I'm sorry if it was a stupid question. I'm new to SQL 2000 just learning. You've helped me out tremendously today. Thanks again.
December 16, 2002 at 5:30 pm
Try this, it is simpler and might be quicker:
DECLARE @WORKPHONE AS VARCHAR(12)
SELECT @WORKPHONE = '1234567890'
SELECT @WORKPHONE = STUFF( STUFF( WORKPHONE, 7, 0, '-' ) , 4, 0, '-' )
SELECT @WORKPHONE
April 13, 2005 at 1:51 pm
To Anyone,
Hope this is useful to someone else.
Later on.. a project to standardize our phone number presentation came up. One app or even one report, one way another app or report, another way, from the datasource. So, instead of doing this in the various apps, I came up with three functions to use from views. The phone numbers are obviously US only, and just being presented in different formats.
Create the three functions in Pubs then execute the select statment below.
USE Pubs
Select phone,
dbo.udf_PhoneFormat('NATIONAL', phone) as '1st',
dbo.udf_PhoneFormat('INTERNATIONAL', phone) as '2nd',
dbo.udf_PhoneFormat('SAP', phone) as '3rd',
dbo.udf_PhoneFormat('RAW', phone) as '4th',
dbo.udf_PhoneFormat('USFORMAT', phone) as '5th'
From authors
CREATE FUNCTION udf_PhoneNumeric
( --udf
@Phone AS NVARCHAR(50)
) --udf
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @Position INT, @String CHAR(50), @Ascii INT, @Char CHAR(1)
SET @position = 1
SET @Ascii = 0
SELECT @string = ltrim(rtrim((@phone)))
SET @Phone = ''
WHILE @position <= LEN(@string)
BEGIN
SELECT @Ascii = ASCII(SUBSTRING(@string, @position, 1)), @Char = SUBSTRING(@string, @position, 1)
IF @Ascii between 48 and 57
begin
SET @Phone = @Phone + '' + @Char
end
SET @position = @position + 1
END
RETURN(@PHONE)
END --udf
CREATE FUNCTION udf_PhoneClean
( --udf
@Phone AS NVARCHAR(50)
) --udf
RETURNS NVARCHAR(50) --udf
AS
BEGIN --udf
DECLARE @Position INT, @String CHAR(50), @Ascii INT, @Char CHAR(1)
SET @position = 1
SET @Ascii = 0
SELECT @string = ltrim(rtrim((@phone)))
SET @Phone = ''
WHILE @position <= LEN(@string)
BEGIN
SELECT @Ascii = ASCII(SUBSTRING(@string, @position, 1)), @Char = SUBSTRING(@string, @position, 1)
IF @Ascii NOT between 48 and 57 SET @Char='.'
SET @Phone = @Phone + '' + @Char
SET @position = @position + 1
END
SET @Position = PATINDEX('%..%',@Phone)
WHILE @Position > 0
BEGIN
SET @Phone = REPLACE(@Phone,'..','.')
SET @Position = PATINDEX('%..%',@Phone)
END
IF LEFT(@Phone,1) = '.'
BEGIN
SET @Phone = SUBSTRING(@PHONE,2,LEN(@PHONE))
END
IF RIGHT(@Phone,1) = '.'
BEGIN
SET @Phone = SUBSTRING(@PHONE,1,LEN(@PHONE)-1)
END
RETURN(@PHONE)
END --udf
CREATE FUNCTION udf_PhoneFormat
( --udf
@Style AS NVARCHAR(15) = NULL,
@Phone AS NVARCHAR(50) = NULL
) --udf
RETURNS NVARCHAR(50) --udf
AS
BEGIN --udf
IF @Style IS NULL RETURN(-1)
IF @Style Not IN ('NATIONAL','INTERNATIONAL','SAP','RAW','USFORMAT') RETURN(-1)
IF @Phone IS NULL RETURN(-1)
DECLARE @Position INT, @String CHAR(50), @Ascii INT, @Char CHAR(1), @OffSet INT, @rc INT
IF @Style IN ('RAW')
------------------------------------------
--Format into pattern xxxxxxxxxx
------------------------------------------
BEGIN
SELECT @string = ltrim(rtrim((@phone)))
SELECT @phone = dbo.udf_PhoneNumeric(@String)
END
ELSE IF @Style IN ('NATIONAL')
BEGIN
SELECT @string = ltrim(rtrim((@phone)))
SELECT @phone = dbo.udf_PhoneNumeric(@String)
if len(@phone) = 10 SET @OffSet = 0
if len(@phone) = 11 SET @OffSet = 1
IF LEN(@phone) between 10 and 11
BEGIN
------------------------------------------
--Format into pattern x(xxx)xxx-xxxx
------------------------------------------
SET @position = 1
SET @Ascii = 0
SELECT @string = ltrim(rtrim((@phone)))
SET @Phone = ''
WHILE @position <= LEN(@string)
BEGIN
SELECT @Char = SUBSTRING(@string, @position, 1)
IF @Position = 1 AND @OffSet = 1
BEGIN
IF @Char = '1'
BEGIN
SET @Phone = '('
END
ELSE
BEGIN
SET @Phone = @Char + '('
END
END
ELSE IF @Position = 1 AND @OffSet = 0
BEGIN
SET @Phone = '(' + @Char
END
ELSE IF @Position = 3 AND @OffSet = 0
BEGIN
SET @Phone = @Phone + '' + @Char + ')'
END
ELSE IF @Position = 4 AND @OffSet = 1
BEGIN
SET @Phone = @Phone + '' + @Char + ')'
END
ELSE IF @Position = 7 AND @OffSet = 0
BEGIN
SET @Phone = @Phone + '-' + @Char
END
ELSE IF @Position = 8 AND @OffSet = 1
BEGIN
SET @Phone = @Phone + '-' + @Char
END
ELSE
BEGIN
SET @Phone = @Phone + '' + @Char
END
SET @position = @position + 1
END
END
END
ELSE IF @Style IN ('INTERNATIONAL')
------------------------------------------
--Format into pattern xxx.xxx.xxxx
------------------------------------------
BEGIN
SELECT @string = ltrim(rtrim((@phone)))
SELECT @phone = dbo.udf_PhoneNumeric(@String)
IF LEN(@phone) = 10
BEGIN
SET @position = 1
SET @Ascii = 0
SELECT @string = ltrim(rtrim((@phone)))
SET @Phone = ''
WHILE @position <= LEN(@string)
BEGIN
SELECT @Char = SUBSTRING(@string, @position, 1)
IF @Position = 1
BEGIN
SET @Phone = @Char
END
ELSE IF @Position = 3
BEGIN
SET @Phone = @Phone + '' + @Char + '.'
END
ELSE IF @Position = 7
BEGIN
SET @Phone = @Phone + '.' + @Char
END
ELSE
BEGIN
SET @Phone = @Phone + '' + @Char
END
SET @position = @position + 1
END
SET @Phone = REPLACE(@Phone,'..','.')
END
ELSE
BEGIN
SELECT @Phone = @String
SELECT @Phone = dbo.udf_PhoneClean(@Phone)
END
END
ELSE IF @Style IN ('SAP')
------------------------------------------
--Format into pattern [x-]xxx-xxx-xxxx
------------------------------------------
BEGIN
SELECT @string = ltrim(rtrim((@phone)))
SELECT @phone = dbo.udf_PhoneNumeric(@String)
IF LEN(@phone) = 10
BEGIN
SET @position = 1
SET @Ascii = 0
SELECT @string = ltrim(rtrim((@phone)))
SET @Phone = ''
WHILE @position <= LEN(@string)
BEGIN
SELECT @Char = SUBSTRING(@string, @position, 1)
IF @Position = 1
BEGIN
SET @Phone = @Char
END
ELSE IF @Position = 3
BEGIN
SET @Phone = @Phone + '' + @Char + '-'
END
ELSE IF @Position = 7
BEGIN
SET @Phone = @Phone + '-' + @Char
END
ELSE
BEGIN
SET @Phone = @Phone + '' + @Char
END
SET @position = @position + 1
END
END
ELSE
BEGIN
SELECT @Phone = @String
SELECT @Phone = dbo.udf_PhoneClean(@Phone)
SET @Phone = REPLACE(@Phone,'.','-')
END
END
ELSE IF @Style IN ('USFORMAT')
------------------------------------------
--Format into pattern x xxx xxx xxxx
------------------------------------------
BEGIN
SELECT @String = dbo.udf_PhoneClean(@Phone)
SET @Phone = REPLACE(@String,'.',' ')
END
RETURN(@PHONE)
END --udf
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply