需要以下代码的程序，即从文本中提取Rs

Question

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;

Answer 1

我想你想要这样的东西：

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;

这只需将您当前的逻辑放入一个函数中即可使用。例如SELECT dbo.myfunction('INCOME FROM RETAIL SALE OF VEGETABLE BUSINESS RS 30,000/-')或SELECT dbo.myfunction(mycolumn) FROM mytable

这是一把小提琴。