CODEWITHSUNDEEP
sql-servert-sql
Hussain Nasif
Hussain Nasif

Reputation: 101

How can I validate if a string contain specific chars range

Here is my function to convert base to base numbers by Input Value, Base of Input, Base to Convert

THE CODE:

Create Function dbo.Unit_Base_To_Base(@Input as nvarchar(60),@Base as int, @NewBase as int)
Returns nvarchar(450)
as begin
Declare @Chartable table(Value  int,Char nvarchar(100)) 
Declare @list Table(val bigint) 
DECLARE @I bigint = 1, 
@Formula bigint, 
@I2 bigint=1, 
@Formula2 varchar(100)
Declare @Ilength bigint = (cast(len(@Input) as nvarchar(60)))
Declare @I2length2 bigint
--------------Value Table
Insert into @Chartable(Value,char)
Values(0,'0')
, (1,'1')
, (2,'2')
, (3,'3')
, (4,'4')
, (5,'5')
, (6,'6')
, (7,'7')
, (8,'8')
, (9,'9')
, (10,'A')
, (11,'B')
, (12,'C')
, (13,'D')
, (14,'E')
, (15,'F')
, (16,'G')
, (17,'H')
, (18,'I')
, (19,'J')
, (20,'K')
, (21,'L')
, (22,'M')
, (23,'N')
, (24,'O')
, (25,'P')
, (26,'Q')
, (27,'R')
, (28,'S')
, (29,'T')
, (30,'U')
, (31,'V')
, (32,'W')
, (33,'X')
, (34,'Y')
, (35,'Z')

------------Convertion Start to 10


SET @Formula = ((Power(convert(bigint,@Base) , (@Ilength-1)))*(Cast( (select Value from @Chartable where char=(SUBSTRING(@input,1,1))) as bigint)))
WHILE @I <= (cast(@Ilength as bigint))
BEGIN
insert into @list(val)
values(@Formula)
    SET @I = @I + 1
     SET @Formula =  (cast((Power(convert(bigint,@Base) , (@Ilength-@I)))*(Cast( (select value from @Chartable where char =(SUBSTRING(@input,@I,1))) as bigint))as bigint))
END

------------Convertion Start to base
Declare @I2nput2 as nvarchar(60) = (select sum(val) from @list)
If @newbase<10 begin set @I2length2 = LOG(@I2nput2,@newbase)+1 end else begin  set @I2length2 = (cast(len(@I2nput2) as bigint)) end
SET @Formula2 = (select char from @Chartable where value=(cast(((CAST(@I2nput2 as bigint))-(((CAST(@I2nput2 as bigint))/(Power(convert(bigint,@newbase) , (@I2length2+1)))))*(Power(convert(bigint,@newbase) , (@I2length2))))/(Power(convert(bigint,@newbase) , (@I2length2-1))) as nvarchar(450))))
WHILE @I2 <= (cast(@I2length2 as bigint))
BEGIN

    SET @I2 = @I2 + 1
     SET @Formula2 =  @Formula2 + (select char from @Chartable where value=(cast(Cast(Isnull(((CAST(@I2nput2 as bigint))-(((CAST(@I2nput2 as bigint))/(Power(convert(bigint,@newbase) , (@I2length2-@I2+1)))))*(Power(convert(bigint,@newbase) , (@I2length2-@I2+1))))/ (isnull(Nullif(Power(convert(bigint,@newbase) , (@I2length2-@I2)),0),1)) ,'') as varchar(100)) as nvarchar(450))))
END
Declare @Result nvarchar(100) =( select replace(ltrim(replace(Left(@Formula2,Len(@Formula2)-1),'0',' ')),' ','0'))
Return @Result
End

Now if i run

select dbo.Unit_Base_To_Base('101',2, 10)

Then Returns 5 which is ok with a valid base 2 input value

And

select dbo.Unit_Base_To_Base('FFF',2, 10)

Then Returns 105 which is a result for invalid base 2 input value

I need to make sure the input value is valid for the base value else return an error as result

Thanks in Advance

Upvotes: 1

Views: 113

Answers (1)

jean
jean

Reputation: 4350

Interesting question. You can decompose you input string and put each char on it in a loop to check if it is greater than the "max char" allowed. You can get that by using char(x) function.

http://technet.microsoft.com/pt-br/library/ms187323.aspx

below some pseudcode (C# style)

function validinputstring(stringInput, base)
{
    foreach charX in stringInput
    {
       is charX > char(base+offset)
    }
}

that offset means: '1' = char(2+offset) && 'F' = char(16+offset) etc

Upvotes: 1

Related Questions