How to get Numbers from Name to prevent conversion failed ?

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

This is how we extract numeric from alpha numerics, create a UDF and use anywhere you want

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString'; // op:- empty column
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@'; //op:- 12341234
SELECT dbo.udf_GetNumeric('123456') AS '123456'; //op:-123456
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf'; //op:- empty
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL'; //op:- 0
GO

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary5y ago
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
Sachin Singh5y ago
This is how we extract numeric from alpha numerics, create a UDF and use anywhere you want
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';   // op:- empty column
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@'; //op:- 12341234
SELECT dbo.udf_GetNumeric('123456') AS '123456'; //op:-123456
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf'; //op:- empty
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL'; //op:- 0
GO