using a user defined function to validate data 1

[warby1212]

Hi, Thanks to all those who replied earlier. I'm still very stuck. (And sorry I wasn't clear the first time.)

I have to use a user defined function to check that a string is correctly formatted. ie. the first character must be a letter and after that they must be numbers. The function returns either 'OK' or 'NotOK'.

I have written this so far (not much)

CREATE FUNCTION dbo.CheckForOrderID (@vc1 VARCHAR(50)
)
RETURNS varchar(50) AS 
    BEGIN 
	DECLARE @okStatus varchar (50)
	SET @okStatus = ''
	
    IF @vc1 = 'O12345'
	     SET @okStatus = 'OK'
	ELSE
	     SET @okStatus ='Not OK'
   	
        RETURN @okStatus
	END

I am totally in the dark here. can anyone help?

thanks Stephen

warby1212 or "Steve". Way away in Australia

 

[dmcmunn]

CREATE FUNCTION dbo.CheckForOrderID (@vc1 VARCHAR(50)
)
RETURNS varchar(50) AS
BEGIN
DECLARE @okStatus varchar (50)
SET @okStatus = 'Not OK'

IF LEN(@vc1) > 0)
AND @vc1 like '[A-Za-z]%'
AND ISNUMERIC(RIGHT(@vc1,LEN(RTRIM(@vc1))-1))
SET @okStatus = 'OK'
END

RETURN @okStatus

 

[warby1212]

Thanks.

YOU ARE A LEGEND!

stephen

warby1212 or "Steve". Way away in Australia

 

[iker3000]

Hi!

The Code in the answer would return OK For the following string:

'a123.234' because 123.234 is numeric. I would suggest the following sollution:

CREATE FUNCTION dbo.CheckForOrderID (@vc1 VARCHAR(50))
RETURNS varchar(50) AS
BEGIN
DECLARE @okStatus varchar (50)

DECLARE @like_string nvarchar(1000)
SET @like_string = '[A-Za-z]' +
REPLICATE('[0-9]', LEN(@vc1) - 1)

IF @vc1 LIKE @like_string
SET @okStatus = 'OK'
ELSE
SET @okStatus = 'Not OK'

RETURN @okStatus


IKER