CODEWITHSUNDEEP
sql-server
Chrislaar123
Chrislaar123

Reputation: 321

Retrieving numerical values from a Nvarchar column

I have a table with a nvarchar column called Custom#5 which has the following data.

 row number  Custom#5   
    1             267.5
    2             tbc
    3
    4             34

I want to be able to clean this data up so always returns a numerical value.

 row number  Custom#5   
    1              267.5
    2              0
    3              0
    4             34

My current query is;

   SELECT CASE 
        WHEN BomHeaders_1.Custom#5 NOT LIKE '%[^0-9]%'
            THEN 0
        WHEN BomHeaders_1.Custom#5 IS NULL
            THEN 0
        ELSE BomHeaders_1.Custom#5
        END AS Custom5
FROM [FS25-W2K8\SQLEXPRESS].sagel50_46772.dbo.BomHeaders AS BomHeaders_1
INNER JOIN [FS25-W2K8\SQLEXPRESS].sagel50_46772.dbo.BomComponents AS BomComponents_1 ON BomHeaders_1.ID = BomComponents_1.HeaderID
INNER JOIN [FS25-W2K8\SQLEXPRESS].sagel50_46772.dbo.BomHeaders AS BomHeaders_2 ON BomComponents_1.StockCode = BomHeaders_2.BomReference
INNER JOIN manu_STOCK ON BomHeaders_1.BomReference = manu_STOCK.STOCK_CODE
WHERE (BomComponents_1.StockCode LIKE N'21%')

The current error i'm getting with this is "Conversion failed when converting the nvarchar value '267.5' to data type int."

Upvotes: 2

Views: 75

Answers (3)

Ionic
Ionic

Reputation: 3935

If your using SQL Server < 2012 and have no option in using try_parse, you can use this:

DECLARE @string nvarchar(255)
SET @string = 'Hali891236.5€hHalo'

SELECT Substring(
          @string,
          PATINDEX('%[0-9.]%',@string),
          PATINDEX('%[^0-9.]%',
                Substring(
                      @string,
                      PATINDEX(
                            '%[0-9.]%',
                            @string
                      ),
                      LEN(@string)
                )
          )-1
     )
GO

Upvotes: 0

mohan111
mohan111

Reputation: 8865

declare @t table (R INT,C varchar(10))
inSERT INTO @t(R,c)values (1,'267.5'),(2,'tbc'),(3,''),(4,'34')

select R,CASE WHEN C LIKE '%[^a-zA-Z]%' THEN  C ELSE CAST(0 AS VARCHAR) END from @t

Upvotes: 0

Neil P
Neil P

Reputation: 3190

If you are using 2012 or greater, I would use the tryparse function

select coalesce(TRY_PARSE ( [Custom#5] AS decimal(18,2)),0)

Upvotes: 3

Related Questions