How to validate Mobile phone Nos by using UDF in MS-SQL

Question

User-582711651 posted

Hi Friends, 

How to validate and save Indian Mobile Phone numbers using UDF in SQL 

Want to check the following aspects before saving the input Nos in SQL with a return message.

Conditions: 

1. Inputs numbers should be 10 digits return message will be 'Valid No'
2. When the mobile number starts with 9,8,7,6  - retrun Message 'Valid No'
3. When the mobile number starts with 0,1,2,3,4,5  - return Message 'Invalid No Starts from 0-5'
4. When mobile number = 000000000 - return message 'Invalid No'
5. When the mobile number is less than 10 digits - 'Invalid No Less than 10 digits'
6. When the mobile number is more than 10 digits - 'Invalid No Morethan 10 digits'
7. When 0 or Null then - 'Invalid No - Null'

Thanks in advance. 

Answer 1

User1535942433 posted

Hi ayyappan.CNN,

As far as I think,you could refer to below article:

https://www.codeproject.com/Articles/20596/Telephone-Numbers-in-SQL-Server-2005-Part-2-Format

https://www.sqlservercentral.com/forums/topic/phone-number-validation-udf

By the way,if you write the UDF you can and should add it as a constraint.You could write a function using LIKE and stuff.

More details,you could refer to below article:

https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112972

Best regards,

Yijing Sun

Answer 2

User-582711651 posted

HI yij sun, 

As per your guidelines, I have developed a UDF, please check and share your advice on this, anything needs to change pls do it;

SELECT dbo.MobPhNoVali('19451116789')
SELECT dbo.MobPhNoVali('6411167-89')
SELECT dbo.MobPhNoVali('')
SELECT dbo.MobPhNoVali('0000000000')

CREATE FUNCTION dbo.MobPhNoVali(@VALUE AS VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @LEN AS BIGINT
    DECLARE @RESULT AS VARCHAR(MAX)
	SET @RESULT = ''

	SET @VALUE = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@VALUE,'-',''),'\r\n',' '),'\r',' '),'\n',' '),CHAR(13),' '), CHAR(10), ' '), CHAR(9),' '),CHAR(13)+CHAR(10),' ')))
    
	IF ISNUMERIC(@VALUE)=1
	BEGIN
	SET @LEN = LEN(@VALUE)

	IF @LEN > 0
	BEGIN
	
	IF @LEN <10
	BEGIN
	SET @RESULT = @RESULT+'Er: Mob.Phone# shn''t Lessthan 10 Digits; '
	END

	IF @LEN >10
	BEGIN
	SET @RESULT = @RESULT+'Er: Mob.Phone# shn''t Morethan 10 Digits; '
	END

	IF LEFT(CAST(@VALUE AS VARCHAR), 1)='0' OR LEFT(CAST(@VALUE AS VARCHAR), 1)='1' OR LEFT(CAST(@VALUE  AS VARCHAR), 1)='2' OR LEFT(CAST(@VALUE AS VARCHAR), 1)='3' or LEFT(CAST(@VALUE AS VARCHAR), 1)='4' or LEFT(CAST(@VALUE AS VARCHAR), 1)='5'
	BEGIN
	SET @RESULT = @RESULT+'Er: Mob.Phone# shn''t Start with 0 - 5; '
	END

	END
	ELSE
	BEGIN
	SET @RESULT = @RESULT+'Er: Mob.Phone# shn''t  0 | NULL; '
	END

	END
	ELSE
	BEGIN
	SET @RESULT = @RESULT+'Er: This is not a valid Mob.Phone#; '
	END
	
	IF @RESULT=''
	BEGIN
	SET @RESULT='VALID NO'
	END
	
RETURN @RESULT
END
GO

 

Answer 3

User1535942433 posted

Hi ayyappan.CNN,

Accroding to your codes,your code looks okay!Do you have tried it?

By the way,are you care of format?Just like (+91)-973132423,9731789889,[phone].

Best regards,

Yijing Sun

Answer 4

User-582711651 posted

Hi Yijing Sun,

Yes, Sure.

Thanks

:)