April 4, 2014 at 2:23 pm
Hello Everyone
Happy Friday
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
Do you happen to know of something else that I can try? This kind of thing working with Oracle or DB2 drive me crazy.
Thank you in advance for your comments, suggestions and time
Andrew SQLDBA
April 5, 2014 at 3:43 am
AndrewSQLDBA (4/4/2014)
Hello EveryoneHappy Friday
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
Do you happen to know of something else that I can try? This kind of thing working with Oracle or DB2 drive me crazy.
Thank you in advance for your comments, suggestions and time
Andrew SQLDBA
Test for the character code and if not within the digit (48-57) range, replace with 0
😎
/* table variable for sample data */
DECLARE @NULLSTR TABLE
( NS_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,STR_VAL VARCHAR(12) NULL
);
INSERT INTO @NULLSTR (STR_VAL)
/* Insert numbers, null and null replacement characters */
VALUES ('0'),('9'),(''),(NULL),(CHAR(32)),(CHAR(0)),('456');
SELECT
NS_ID AS NS_ID
,STR_VAL AS STR_VAL
,DATALENGTH(STR_VAL) AS DATALENGTH_STR_VAL
/* unicode function returns the character code of the first/only character */
,UNICODE(STR_VAL) AS UNICODE_STR_VAL
,ISNULL(UNICODE(STR_VAL),0) AS ISNULL_UNICODE_STR_VAL
,CASE /* if not in the number range 48-57 then return 0 else cast to int */
WHEN ISNULL(UNICODE(STR_VAL),0) BETWEEN 48 AND 57 THEN CAST(STR_VAL AS INT)
ELSE 0
END AS INT_STR_VAL
FROM @NULLSTR
Result set;
NS_ID STR_VAL DATALENGTH_STR_VAL UNICODE_STR_VAL ISNULL_UNICODE_STR_VAL INT_STR_VAL
----------- ------------ ------------------ --------------- ---------------------- -----------
1 0 1 48 48 0
2 9 1 57 57 9
3 0 NULL 0 0
4 NULL NULL NULL 0 0
5 1 32 32 0
6 1 0 0 0
7 456 3 52 52 456
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply