How to get Numbers from Name to prevent conversion failed ?

Question

I work on SQL server 2012 I Face issue as below :

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value '1.2kV' to data type int.

 

this error done where converting Name to number but it is failed conversion.

 

as Example Name have value 1.2v then if i get numbers from name so will be 1.2 and on this case will not get error

 

and if i found N/A convert to 0 .

 

1.2kv,280vdc,100v, 24vac/dc,N/A get number from that and if n/a convert to 0

 

so How to get Numbers from Name to prevent it from display this error .

if object_id(N'tempdb..#Condition') is not null drop table #Condition


if object_id(N'tempdb..#Allfeatures') is not null drop table #Allfeatures
if object_id(N'tempdb..#Codes') is not null drop table #Codes

create table #Allfeatures
   (
    ZPLID INT,
    ZFeatureKey nvarchar(20),
    IsNumericValue int
   )
   insert into #Allfeatures(ZPLID,ZFeatureKey,IsNumericValue)
   values(75533,'1505730036',0)

   create table #Condition
   (
    Code nvarchar(20),
    ZFeatureKey nvarchar(20),
    ZfeatureType nvarchar(20),
    EStrat  nvarchar(20),
    EEnd NVARCHAR(10)
   )
   insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)
   values
   ('8535400000','1505730036',NULL,'>1000',' '),
   ('8535400000','1505730036',NULL,'>280AV',' '),
   ('8535400000','1505730036',NULL,'N/A',' '),
   ('8535400000','1505730036',NULL,NULL,' ')

   CREATE TABLE #PartAttributes
   (
    PartID INT,
    ZFeaturekEY NVARCHAR(20),
    AcceptedValuesOption_Value  INT,
    Name nvarchar(20)
   )
   insert into #PartAttributes(PartID,ZFeaturekEY,AcceptedValuesOption_Value,Name)
   values
   (4977941,1505730036,280,'1.2kV'),
   (4977936,1505730036,280,'280VDC'),
   (4977935,1505730036,280,'100V'),
   (4977808,1505730036,280,'N/A'),
   (4979054,1505730036,280,'24VAC/DC')

    DECLARE @Sql nvarchar(max)
    DECLARE @ConStr nvarchar(max)

   SET @ConStr = STUFF((
   SELECT CONCAT(' OR (PM.ZfeatureKey = ', CC.ZfeatureKey, IIF(CC.ZfeatureType = 'Qualifications', ' And AcceptedValuesOption_Value ', ' And replace(Name, ''VDC'', space(4)) '),
   CASE
   WHEN EStrat = 'N/A' THEN '= ''N/A'''
   ELSE CAST(LEFT(SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500), PATINDEX('%[^<>0-9.-]%', SUBSTRING(EStrat, PATINDEX('%[<>0-9.-]%', EStrat), 2500) + 'X') -1) AS nvarchar(2500))
   END, ')')
      FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue = 0
     WHERE EStrat IS NOT NULL
   FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'), 1, 3, '')

    ----------------
    SET @Sql= CONCAT(' SELECT PartID, Code, Count(1) as ConCount
    FROM #PartAttributes PM
    INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',
    'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,
    ' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))

    EXEC (@SQL)

this statement generating from dynamic SQL that have issue

SELECT PartID, Code, Count(1) as ConCount
FROM #PartAttributes PM
INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey Where 1=1 and ( (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4)) >1000) OR (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4)) >280) OR (PM.ZfeatureKey = 1505730036 And replace(Name, 'VDC', space(4)) = 'N/A') ) Group By PartID,Code Having Count(1)> = 4