Extracting a price from a VARCHAR in T-SQL

CREATE FUNCTION [dbo].[fn_extract_currency_from_string] (@string VARCHAR(1000))
RETURNS VARCHAR(255)
AS
BEGIN

	DECLARE @var VARCHAR(1000) = @string
	DECLARE @len INT = LEN(@var)

	DECLARE @start_position INT
		,@end_position INT

	DECLARE @temp_string VARCHAR(100)

	SELECT @start_position = PATINDEX('%£%', @var)

	SELECT @temp_string = SUBSTRING(@var, @start_position + 1, @len)

	SELECT @end_position = PATINDEX('% %', @temp_string)

	-- Done
	RETURN substring(@temp_string, 1, CASE @end_position WHEN 0 THEN @len ELSE @end_position -1 END)
END

 

Leave a Reply