-1
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
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Sridhar Babu
  • 1
  • 2
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/247911/discussion-on-question-by-sridhar-babu-stored-procedure-to-extract--symbol-wher). – Samuel Liew Sep 09 '22 at 09:09

2 Answers2

0

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;
herostoky
  • 107
  • 1
  • 8
0

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

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • If we have value with comma eg ₹35,000, the output coming is 35 only, we have to get 35000. – Sridhar Babu Sep 11 '22 at 10:30
  • that will not be easy, in my country the comma means decimal separator, so 35,000 = 35 but there are still few countrys where the comma means thousand separator, so 35,000 = 35000. How will you know if the comma in the text is a decimal seperator or a thousand separator ? – GuidoG Sep 12 '22 at 09:08
  • If you are 100% sure it will always be a thousand seperator, just add this `set @promo_text = replace(@promo_text, ',', '')` see also [here](https://dbfiddle.uk/HPwZcjqF) – GuidoG Sep 12 '22 at 09:12
  • Thank you, Is the same cannot be executed in procedure. In function its fine. – Sridhar Babu Sep 12 '22 at 10:13
  • I do not understand your last comment – GuidoG Sep 12 '22 at 11:00