How to get Numbers from Name to prevent conversion failed?

Transact-SQL
,
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 .

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)

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
2

select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))

also include . etc as you need

3

Hi @ahmedbarbary

Does this function helps your cause.


create Function [dbo].[fn_GetNumbers2](@Data VarChar(8000))
Returns VarChar(8000)
AS
Begin
set @Data =  Left(
             SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000), 
             PatIndex('%[^0-9.-]%', SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000) + 'X')-1)
if ISNUMERIC(@data)<> 1 set @Data='0'          
return (@data)
End

/*

Declare @Temp Table(Data VarChar(60))

Insert Into @Temp Values('2.1 miles')
Insert Into @Temp Values('4 miles')
Insert Into @Temp Values('Approximately 6.5 miles')
Insert Into @Temp Values('3.9')
Insert Into @Temp Values('7.2miles')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)
Insert Into @Temp Values('No Numbers Here')
Insert Into @Temp Values('approximately 2.5 miles, but less than 3')
Insert Into @Temp Values('integer 22xx')
Insert Into @Temp Values('N/A')


Select Data, dbo.fn_GetNumbers2(Data)
From   @Temp
*/
4

thank you for reply
function is good but hot to modify dynamic query generated @SQL
with another meaning how to use it on @ConStr to prevent error from happen
can you help me please