phone no validation in sql server

Question

how to check stored phone number is a valid phone number in sql server...

Sunny Sharma · Answer

Hi vasanth, Create a UDF and validate phone number with that function. Here you go: --------------------------------------------------- CREATE FUNCTION [dbo].[udf_ValFormatPhone] (@phone NVARCHAR(255)) RETURNS NVARCHAR(255) AS BEGIN--function declare @i int, @repCount int declare @current_char char(1) declare @phone_new varchar(50) set @phone_new = rtrim(ltrim(@phone)) if left(@phone_new, 1) = '1' set @phone_new = right(@phone_new, len(@phone_new) -1) set @i = 1 while @i begin set @repCount = 0 if @i > len(@phone_new) break set @current_char = substring(@phone_new, @i, 1) if isnumeric(@current_char) 1 begin set @repCount = len(@phone_new) - len(replace(@phone_new, @current_char, '')) set @phone_new = replace(@phone_new, @current_char, '') end set @i = @i + 1 - @repCount end if isnumeric(@phone_new) = 1 and len(@phone_new) = 10 set @phone_new = substring(@phone_new, 1,3) + '-' + substring(@phone_new, 4,3) + '-' + substring(@phone_new, 7,4) else set @phone_new = 'invalid entry' return @phone_new END --function ---------------------------------------------------- Taken from here . Hope it helps :)

Veena Sarda · Answer

For which country and what language is you User Interface built in?

phone no validation in sql server

Answers (2)