2
Answers

How to get Numbers from Name to prevent conversion failed ?

ahmed elbarbary

ahmed elbarbary
4y
505
1
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 .
  1. if object_id(N'tempdb..#Condition') is not null drop table #Condition    
  2.        
  3.        
  4. if object_id(N'tempdb..#Allfeatures') is not null drop table #Allfeatures    
  5. if object_id(N'tempdb..#Codes') is not null drop table #Codes    
  6.        
  7. create table #Allfeatures    
  8.    (    
  9.     ZPLID INT,    
  10.     ZFeatureKey nvarchar(20),    
  11.     IsNumericValue int    
  12.    )     
  13.    insert into #Allfeatures(ZPLID,ZFeatureKey,IsNumericValue)    
  14.    values(75533,'1505730036',0)    
  15.                                
  16.    create table #Condition    
  17.    (    
  18.     Code nvarchar(20),    
  19.     ZFeatureKey nvarchar(20),    
  20.     ZfeatureType nvarchar(20),    
  21.     EStrat  nvarchar(20),    
  22.     EEnd NVARCHAR(10)    
  23.    )    
  24.    insert into #Condition (Code,ZFeatureKey,ZfeatureType,EStrat,EEnd)    
  25.    values    
  26.    ('8535400000','1505730036',NULL,'>1000',' '),    
  27.    ('8535400000','1505730036',NULL,'>280AV',' '),    
  28.    ('8535400000','1505730036',NULL,'N/A',' '),    
  29.    ('8535400000','1505730036',NULL,NULL,' ')    
  30.                                
  31.    CREATE TABLE #PartAttributes    
  32.    (    
  33.     PartID INT,    
  34.     ZFeaturekEY NVARCHAR(20),    
  35.     AcceptedValuesOption_Value  INT,    
  36.     Name nvarchar(20)    
  37.    )    
  38.    insert into #PartAttributes(PartID,ZFeaturekEY,AcceptedValuesOption_Value,Name)    
  39.    values    
  40.    (4977941,1505730036,280,'1.2kV'),    
  41.    (4977936,1505730036,280,'280VDC'),    
  42.    (4977935,1505730036,280,'100V'),    
  43.    (4977808,1505730036,280,'N/A'),    
  44.    (4979054,1505730036,280,'24VAC/DC')    
  45.                                
  46.     DECLARE @Sql nvarchar(max)    
  47.     DECLARE @ConStr nvarchar(max)    
  48.                                                
  49.    SET @ConStr = STUFF((    
  50.    SELECT CONCAT(' OR (PM.ZfeatureKey = ', CC.ZfeatureKey, IIF(CC.ZfeatureType = 'Qualifications', ' And AcceptedValuesOption_Value ', ' And replace(Name, ''VDC'', space(4)) '),     
  51.    CASE     
  52.    WHEN EStrat = 'N/A' THEN '= ''N/A'''     
  53.    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))     
  54.    END, ')')    
  55.       FROM #Condition CC INNER JOIN #Allfeatures AL ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValue = 0    
  56.      WHERE EStrat IS NOT NULL    
  57.    FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'), 1, 3, '')    
  58.                
  59.     ----------------                        
  60.     SET @Sql= CONCAT(' SELECT PartID, Code, Count(1) as ConCount    
  61.     FROM #PartAttributes PM     
  62.     INNER JOIN #Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',    
  63.     'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,    
  64.     ' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))    
  65.            
  66.     EXEC (@SQL)    
this statement generating from dynamic SQL that have issue
  1. SELECT PartID, Code, Count(1) as ConCount  
  2. FROM #PartAttributes PM  
  3. 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  
Answers (2)
Next Recommended Forum