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 .

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

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

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


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)

Answer 1

thank you for reply
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

error i get
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1.2kV' to data type int.

the issue is 1.2v or another values that have text with numbers on Name as Example

1.2v when compare with number give error conversion above .

same thing for another values as 100v and 24VAC/DC

so What I do to solve issue

also N/A is text so we can convert it to 0

Answer 2

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''' 
    WHEN LEFT(EStrat, 1) = '>' THEN LEFT(EStrat, 1) + '''' + SUBSTRING(EStrat, 2, LEN(EStrat) - 1) + ''''
    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, '')

Answer 3

Hi @ahmed salah ,

Replace(Name,'VDC', space(4)) is of type nvarchar and cannot be directly compared with numbers (1000, 280). You need to extract the number in name and convert it to int type.

IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL  
DROP FUNCTION DBO.GET_NUMBER2  
Go  
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))  
RETURNS VARCHAR(100)  
AS  
BEGIN  
WHILE PATINDEX('%[^0-9]%',@S) > 0  
BEGIN  
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')  
END  
RETURN @S  
END  
GO  
  
  
;with cte  
as(select name,case when replace(Name, 'VDC', space(4))='N/A' then 0  
 else DBO.GET_NUMBER2(Name) end num  
from #PartAttributes)  
,cte2 as(select name,cast(num as int) num2 from cte)  
  
  
SELECT PartID, Code, Count(1) as ConCount  
FROM #PartAttributes PM   
INNER JOIN #Condition Co   
ON Co.ZfeatureKey = PM.ZfeatureKey   
inner join cte2 c2 on PM.name=c2.name  
Where 1=1 and ((PM.ZfeatureKey = 1505730036 And num2  >1000)  
OR (PM.ZfeatureKey = 1505730036 And num2 >280)   
OR (PM.ZfeatureKey = 1505730036 And replace(PM.Name, 'VDC', space(4)) = 'N/A') )  
Group By PartID,Code    
Having Count(1)> = 4  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

thank you for reply

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


 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)

    ;with cte
 as(select name,case when replace(Name, 'VDC', space(4))='N/A' then 0
  else dbo.GET_NUMBER2(Name) end num
 from #PartAttributes)
 ,cte2 as(select name,cast(num as int) num2 from cte)





--select * from  cte

    SET @ConStr = STUFF((
    SELECT CONCAT(' OR (PM.ZfeatureKey = ', CC.ZfeatureKey, IIF(CC.ZfeatureType = 'Qualifications', ' And AcceptedValuesOption_Value ', ' And num2) '), 
    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 
      inner join cte2 c2 on PM.name=c2.name',
     'Where 1=1 and (', @ConStr, ' ) Group By PartID,Code ' ,
     ' Having Count(1)> = ',(SELECT COUNT(1) FROM #Condition))

     EXEC (@SQL)

i try as above script get error

incorrect syntax near Set

i don't know why this issue happen

how to solve issue

Answer 5

i need only to solve issue on script above to test result
why give me incorrect syntax near Set
do please help
on script above i try test solution you give me with cte on last post