CREATE FUNCTION [dbo].[FindNegative] (@Number varchar(30))
  RETURNS decimal(18, 2)
AS
BEGIN

IF @Number = ''
  SET @Number = NULL
ELSE
  BEGIN
    DECLARE @Letter varchar(1)
    DECLARE @NewNumber varchar(1)

    SET @Letter = RIGHT(@Number, 1)

    SELECT @NewNumber = CASE
      WHEN @Letter = '}' THEN '0'
      WHEN @Letter = 'J' THEN '1'
      WHEN @Letter = 'K' THEN '2'
      WHEN @Letter = 'L' THEN '3'
      WHEN @Letter = 'M' THEN '4'
      WHEN @Letter = 'N' THEN '5'
      WHEN @Letter = 'O' THEN '6'
      WHEN @Letter = 'P' THEN '7'
      WHEN @Letter = 'Q' THEN '8'
      WHEN @Letter = 'R' THEN '9'
      END

    IF @Letter <> @NewNumber
      BEGIN
        SET @Number = LEFT(@Number, LEN(@Number)-1)
        SET @Number = @Number + @NewNumber
        SET @Number = CONVERT(decimal(18, 2), @Number) * -1
      END
    ELSE
      SET @Number = CONVERT(decimal(18, 2), @Number)
  END

RETURN @Number

END

--> For testing
DECLARE @table table (Col varchar(30))
INSERT INTO @table
SELECT '000035}' UNION ALL SELECT '00045874' UNION ALL SELECT '00659871R'

SELECT Col, dbo.FindNegative(Col)
FROM @table

Expected output:
Col                            NewCol
------------------------------ ---------------------------------------
000035}                        -350
00045874                       45874
00659871R                      -6598719

CREATE FUNCTION [dbo].[FindNegative] (@Number varchar(30))
  RETURNS decimal(18, 2)
AS
BEGIN

IF @Number = ''
  SET @Number = NULL
ELSE
  BEGIN
    DECLARE @Letter varchar(1)
    DECLARE @NewNumber varchar(1)

    SET @Letter = RIGHT(@Number, 1)
    
    SELECT @Number = CASE
      WHEN @Letter = '}' THEN 
		Convert(Decimal(18,2),Replace(@Number,'}','0')) * -1 
      WHEN ASCII(@LETTER) BETWEEN ASCII('J') AND ASCII('R') THEN 
		 Convert(Decimal(18,2),Replace(@Number,@Letter,Convert(char(1),1 + (ASCII(@Letter) - ASCII('J'))))) * -1
      ELSE 
	CONVERT(decimal(18, 2), @Number)
      End 	
  END

RETURN @Number

END
GO
DECLARE @table table (Col varchar(30))
INSERT INTO @table
SELECT '000035}' UNION ALL SELECT '00045874' UNION ALL SELECT '00659871R'

SELECT Col, dbo.FindNegative(Col)
FROM @table

--Output.. 


Col                                                 
------------------------------ -------------------- 
000035}                        -350.00
00045874                       45874.00
00659871R                      -6598719.00

CREATE FUNCTION dbo.FindNegative
(
	@Number VARCHAR(30)
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
	IF LEN(@Number) = 0 OR @Number IS NULL
		RETURN NULL

	IF RIGHT(@Number, 1) IN ('}', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R')
		SELECT	@Number = '-' + LEFT(@Number, LEN(@Number) - 1) + CASE WHEN RIGHT(@Number, 1) = '}' THEN '0' ELSE CHAR(ASCII(RIGHT(@Number, 1)) - 25) END

	RETURN CAST(@Number AS DECIMAL(18,2))
END

ALTER FUNCTION dbo.FindNegative
(
	@Number VARCHAR(30)
)
RETURNS DECIMAL(18, 2)
AS
BEGIN
	IF LEN(@Number) = 0 OR @Number IS NULL
		RETURN NULL

	IF RIGHT(@Number, 1) IN ('}', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R')
		SELECT	@Number = '-' + LEFT(@Number, LEN(@Number) - 1) + CHAR(ASCII(RIGHT(@Number, 1)) % 50 + 25)

	RETURN CAST(@Number AS DECIMAL(18,2))
END

quote:

---

Originally posted by Lumbago

*Anything* that will increase performance on this baby is appreciated.

---

ALTER FUNCTION dbo.FindNegative
(
	@Number VARCHAR(30)
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

        DECLARE @SUFFIX VARCHAR(1)
        DECLARE @LEN INT

        SET @LEN = LEN(@Number)
	IF @LEN = 0 OR @Number IS NULL
		RETURN NULL

        SET @SUFFIX = RIGHT(@Number, 1)

	IF @SUFFIX IN ('}', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R')
		SELECT	@Number = '-' + LEFT(@Number, @LEN - 1) + CHAR(ASCII(@SUFFIX) % 50 + 25)

	RETURN CAST(@Number AS DECIMAL(18,2))
END

quote:

---

Originally posted by byrmol

>> Does T-SQL supports Short-Circuit evaluation?

No it does not..

DavidM

Production is just another testing cycle

---

if 1 = 1 or (select avg(number) from f_table_number_range(1, 16777215) where number % 7 = 0) > 0
	select 'true'
else
	select 'false'

use Northwind
go
declare @d int
set @d  = 1

if 1=@d or exists (Select * from dbo.Employees where EmployeeID = 2)
	print 'Check the Execution Plan'

quote:

---

Originally posted by Peso

declare @test table (i int)

insert	@test
select	1 union all
select	2 union all
select	3 union all
select	4 union all
select	5

select	t.*
from	@test t
where	t.i in (1,2,3,4,5) or (select avg(1.0 * q.number) from f_table_number_range(1, 16777215) q where q.number % 7 = 0) > 0

Executes instantly! Run the subquery separately and time it. My time is 13 seconds for the subquery. And for 5 rows my test query should have taken over a minute to run, but it doesn't. It takes less than 0.1 second.


Peter Larsson
Helsingborg, Sweden

---

select avg(q.number) from f_table_number_range(1, 16777215) q where q.number % 7 = 0

quote:

---

Arithmetic overflow error converting expression to data type int.

---

quote:

---

Originally posted by Lumbago
...if the last character in the passed variable is either } or between J and R it means that the number is negative and the caracter has to be replaced with a number equivalent and multiplied by -1!...

---