can't get Numbers from Name to prevent conversion failed ?

Question

Post reply

can't get Numbers from Name to prevent conversion failed ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 16, 2020 at 10:31 pm

#3808463

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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Grab a copy of PatReplace8K and it should be easy to strip out what you need.

SELECT 
  pa.*, 
  Clean1 = CAST(ISNULL(pr.newString,0) AS FLOAT),
  Clean2 = CAST(CAST(ISNULL(pr.newString,0) AS FLOAT) AS INT)
FROM #PartAttributes AS pa
CROSS APPLY samd.patReplace8K(pa.name,'[^0-9.]','') AS pr;

This gives you:

PartID      ZFeaturekEY          AcceptedValuesOption_Value Name    Clean1   Clean2
----------- -------------------- -------------------------- ------- -------  ------ 
4977941      1505730036          280                        1.2kV    1.2     1
4977936      1505730036          280                        280VDC   280     280
4977935      1505730036          280                        100V     100     100
4977808      1505730036          280                        N/A      0       0
4979054      1505730036          280                        24VAC/DC 24      24

Note that I didn't know if you need "1.2V" to be 1 or 1.2.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply

function is good but hot to modify dynamic query generated @sql

with another meaning how to use function above on @ConStr to prevent error from happen

can you help me please

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

If the value is in a variable you need to strip you just pass it direct to the function

DECLARE @ConStr VARCHAR(10) = '1.2Kv'
SELECT Clean1 = CAST(ISNULL(pr.newString,0) AS FLOAT)
FROM samd.patReplace8K(@ConStr,'[^0-9.]','') AS pr;


Output
1.2