SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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'),
('test 123');

SELECT i.clientIncome,
       SUBSTRING(stuff(i.clientIncome, 1, CHARINDEX('₹', i.clientIncome), ''), 
                 PATINDEX('%[0-9]%', stuff(i.clientIncome, 1, CHARINDEX('₹', i.clientIncome), '')), 
                 PATINDEX('%[0-9][^0-9]%', stuff(i.clientIncome, 1, CHARINDEX('₹', i.clientIncome), '') + 't') - PATINDEX('%[0-9]%', 
                 stuff(i.clientIncome, 1, CHARINDEX('₹', i.clientIncome), '')) + 1) 
       AS Income
FROM #income AS i;




clientIncome
Income


 ?30000 for sales of vegetables
30000

?40000 for whole sale of vegetables
40000

TOTAL INCOME APPROX ?70000
70000

NET INCOME  ? 35,000
35

test 123
123


CREATE or ALTER function dbo.StringToNumber (@promo_text nvarchar(200), @symbol nvarchar(1)) returns decimal(16,2) as
begin
     declare @result decimal(16,2) = null
     declare @test nvarchar(100)

     if charindex(@symbol, @promo_text) > 0
     begin
         set @promo_text = substring(@promo_text, charindex(@symbol, @promo_text) + 1, len(@promo_text))

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

         if len(@test) > 0
             set @result = convert(decimal(16, 2), @test)
     end
  
     return @result
end
select i.ClientIncome,
       dbo.StringToNumber(i.ClientIncome, '₹')
from   #income i


ClientIncome
(No column name)


 ?30000 for sales of vegetables
30000.00

?40000 for whole sale of vegetables
40000.00

TOTAL INCOME APPROX ?70000
70000.00

NET INCOME  ? 35,000
35.00

test 123
null


drop function dbo.StringToNumber
DROP TABLE #income;

clientIncome	Income
?30000 for sales of vegetables	30000
?40000 for whole sale of vegetables	40000
TOTAL INCOME APPROX ?70000	70000
NET INCOME ? 35,000	35
test 123	123

ClientIncome	(No column name)
?30000 for sales of vegetables	30000.00
?40000 for whole sale of vegetables	40000.00
TOTAL INCOME APPROX ?70000	70000.00
NET INCOME ? 35,000	35.00
test 123	null