Aniket Narvankar

Aniket Narvankar

  • 833
  • 1.8k
  • 363.2k

SQL Query Solution

May 6 2020 12:01 AM
I have to write a query,in which If I receive a value of
1)length greater than or equal to 4 then I have to add a dot at 4th position and rewrite it.
 2)If it is of length less than four then keep as it is
3)If it already contains dot at 4th position then keep as it is. 
4)but if the dot it is not a 4th position then I have to bring the dot at 4th position and rewrite it.
 
Following is the example of input and output 
 
For example
Value is A456791 then output A45.6791
Value is A456 then  output A45.6
Value is A42 then output A42 
Value is A45.6 then output A45.6 
Value is A.456 then output A45.6
Value is A456.0 then output A45.60
Value is A4567.12 the output A45.6712
 
So far I have written following query
 
declare @str varchar(max)
set @str='A4567891'
if(LEN(@str)>=4)
begin
IF(CHARINDEX('.',@str)>0)
BEGIN
PRINT @STR
END
ELSE
BEGIN
Select SUBSTRING(@str,1,3) + '.' + SUBSTRING(@str,4,LEN(@str))
END
end
else
begin
PRINT 'HI'
end
 
This is working for first 3 points but I am stuck on last point which is  
but if the dot it is not a 4th position then I have to bring the dot at 4th position and rewrite it.
 
That is input is suppose A3119.2 then output should be A31.192
 
How to do this,kindly help me on this 
 
 

Answers (1)