Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to extract numeric and special charcters seperately’

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

extractsymbols1.1

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

extractsymbols1.2

Advertisements

Read Full Post »