Need a procedure for the below code, i.e, extracting Rs. from text

Sridhar Babu 0
Viewed 25
CREATE TABLE #income (clientIncome NVARCHAR(200));

INSERT INTO #income
(
    clientIncome
)
VALUES ('INCOME FROM RETAIL SALE OF VEGETABLE BUSINESS RS 30,000/-'),
('INCOME FROM WHOLE  SALE OF VEGETABLE BUSINESS- RS 40,000/-'),
('TOTAL INCOME APPROX- RS 70,000/-'),
('TOTAL EXPENSES- RS 35,000/-'),
('[SALARIES, FUEL, OTHER EXPENSES]'),
('NET INCOME- RS 35,000/-');

SELECT i.clientIncome,
       CASE
           WHEN CHARINDEX('RS ', i.clientIncome) != 0 THEN
               CAST(replace(SUBSTRING(
                    i.clientIncome,
                    CHARINDEX('RS ', i.clientIncome) + 3,
                    CHARINDEX('/-', i.clientIncome) - CHARINDEX('RS ', i.clientIncome) - 3
                    ),',','') AS NUMERIC)
           ELSE
               NULL
       END income
FROM #income AS i;

DROP TABLE #income;
procedure sql
1 Answers

I think you want something like:

CREATE FUNCTION myfunction(@inputstring nvarchar(max))
RETURNS int
AS
BEGIN
    DECLARE @ret int;
    SET @ret = CASE
           WHEN CHARINDEX('RS ', @inputstring) != 0 THEN
               CAST(replace(SUBSTRING(
                    @inputstring,
                    CHARINDEX('RS ', @inputstring) + 3,
                    CHARINDEX('/-', @inputstring) - CHARINDEX('RS ', @inputstring) - 3
                    ),',','') AS NUMERIC)
           ELSE
               NULL
       END
    RETURN @ret;
END;

This simply puts your current logic in a function which can then be used. e.g. SELECT dbo.myfunction('INCOME FROM RETAIL SALE OF VEGETABLE BUSINESS RS 30,000/-') or SELECT dbo.myfunction(mycolumn) FROM mytable

Here is a fiddle of it.

EdmCoff0
Related