SQL Server Portal

I came across with a new issue related to the legacy system. The situation is that, I have a varchar column in a table having numeric data with the currency and I need to segregate the numeric data and currency symbol. I spent sometime to figure it out and finally did it.

Before proceeding with the solution, let me create a sample to demonstrate the issue.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Curreny] VARCHAR(10)
)
GO
INSERT INTO tbl_sample VALUES (1,'£ 12.95')
INSERT INTO tbl_sample VALUES (2,'$ 20.38')
INSERT INTO tbl_sample VALUES (3,'Rs176.34')
INSERT INTO tbl_sample VALUES (4,'€58')
INSERT INTO tbl_sample VALUES (5,'¥ 33.3')
INSERT INTO tbl_sample VALUES (6,'97800')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Now, lets segregate the numbers and symbols. Given below is the solution.

USE tempdb
GO
SELECT
[ID]
, [Curreny]
, LEFT([Curreny],PATINDEX('%[0-9]%',[Curreny])-1)
AS [Currency Symbol]
, SUBSTRING([Curreny],PATINDEX('%[0-9]%',[Curreny]),LEN([Curreny]))
AS [Numbers]
FROM tbl_sample
--OUTPUT