November 16, 2020 at 10:31 pm
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)
November 17, 2020 at 6:20 pm
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.
-- Itzik Ben-Gan 2001
November 18, 2020 at 7:27 am
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
November 18, 2020 at 8:05 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply