Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blank Space in an Integer Data Type Column View Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 2:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
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
Post #1558648
Posted Saturday, April 5, 2014 3:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 2,533, Visits: 7,100
AndrewSQLDBA (4/4/2014)
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


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
Post #1558708
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse