stored procedure to extract ₹ symbol where ever ₹ in the text and display only number, Eg, ₹12 for t-CodePudding

CREATE TABLE #income (clientIncome NVARCHAR(200));

INSERT INTO #income
(
    clientIncome
)
VALUES (' ₹30000 for sales of vegetables'),
('₹40000 for whole sale of vegetables'),
('TOTAL INCOME APPROX ₹70000'),

('NET INCOME  ₹ 35,000');

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

DROP TABLE #income;

For the above created procedure, getting output NULL

CREATE or ALTER PROCEDURE test (@promo_text varchar(100))
AS
begin
SET NOCOUNT ON
SELECT 
       CASE
           WHEN CHARINDEX('$', @promo_text) != 0 THEN
               CAST(replace(SUBSTRING(
                    @promo_text,
                    CHARINDEX(' $ ', @promo_text)   3,
                    CHARINDEX('/-', @promo_text) - CHARINDEX(' $ ', @promo_text) - 3
                    ),',','') AS NUMERIC) 
           ELSE
               NULL
       END income from income
print @promo_text
end

CodePudding user response：

Why not creating an sql function just to format your column?

In mysql It will look something like :

CREATE FUNCTION `FORMAT_CURRENCY`(str TEXT) RETURNS text CHARSET utf8mb4
BEGIN
    SET str = REPLACE(str,'₹','');
    SET str = REPLACE(str,'$',''); // Just another Example 
    RETURN str;
END

Your select query then will be like :

SELECT i.clientIncome, FORMAT_CURRENCY(i.clientIncome) AS income
FROM #income AS i;

CodePudding user response：

We are guessing here what you want,
I think you want to extract the number from the column ClientIncome, by use of a Function (not a procedure !)
The parameter in the procedure you have in your question makes no sense, but if you actually want a function in stead of a procedure than this parameter would make sense, that is why I think you need a function and not a procedure.

If this is indeed what you want, you could try with this fuction

CREATE or ALTER function dbo.StringToNumber (@promo_text varchar(100)) returns decimal(16,2) as
begin
     declare @result decimal(16,2)

     select @result =
       SUBSTRING(stuff(@promo_text, 1, CHARINDEX('₹', @promo_text), ''), 
                 PATINDEX('%[0-9]%', stuff(@promo_text, 1, CHARINDEX('₹', @promo_text), '')), 
                 PATINDEX('%[0-9][^0-9]%', stuff(@promo_text, 1, CHARINDEX('₹', @promo_text), '')   't') - PATINDEX('%[0-9]%', 
                 stuff(@promo_text, 1, CHARINDEX('₹', @promo_text), ''))   1) 

    return @result
end

you can use it like this

select i.ClientIncome,
       dbo.StringToNumber(i.ClientIncome)
from   #income i

Please look at this DBFiddle and see if this is actually what you want.

I used this question as inspiration for this answer, maybe you can find other usefull information there

EDIT

I altered the function so it only returns a number if the requested symbol is present in the column,
have a look here