Convert MSAccess Query to TSQL

Question

Hello I am trying to convert a MSAccess Query to TSQL.

EDIT FOR ADDITIONAL DETAIL;

A user created an MS Access application using my database as the source tables via ODBC. Per our policy, MS Access is only to be used for adhoc analysis, no storage of data. Well this use created a bunch of new tables in his application and is using it as production data. I have already replicated his logic and moved it to SQL Server via triggered scripts. I am now trying to populate his historical data into SQL.

In my db MATERIAL_NUMBER is a varchar(30) data type. We have all sorts of different conventions from acquisitions.

One of the companies we acquired has PNs such as "000000000000000001". The creator of the Access application did not like that the leading zeros would prompt excel to say "number stored as text" when exported and so in his tables he applied the following to the material number to store "000000000000000001" as "1"

This is the Access Query.

IIf(IsNumeric([MATERIAL_NUMBER])=0,[MATERIAL_NUMBER],CStr(CDbl([MATERIAL_NUMBER]))) AS PN_FORMAT

The issue now is that I can not join his tables to mine as he has broken MATERIAL_NUMBER. What I am trying to do is reverse engineer his conversion so that I can update the values back to the standard convention.

This is what I tried in SQL

CASE WHEN ISNUMERIC([MATERIAL_NUMBER])=1 
   THEN CONVERT(VARCHAR(30),CAST([MATERIAL_NUMBER] AS BIGINT))
   ELSE [MATERIAL_NUMBER] 
END AS PN_FORMAT

I get the following Error

Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to bigint.

Here is the full Query

TRUNCATE TABLE PN_FORMAT;

INSERT INTO PN_FORMAT
SELECT O.BASE_PART_CODE, O.MATERIAL_NUMBER, 
  CASE WHEN ISNUMERIC([MATERIAL_NUMBER])=1 
    THEN CONVERT(VARCHAR(30),CAST([MATERIAL_NUMBER] AS BIGINT))
    ELSE [MATERIAL_NUMBER] 
  END AS PN_FORMAT
FROM [DCA-DB-326\MSBI_RS].[AGS_DATAMART].dbo.OPR_MATERIAL_DIM O

EDIT FOR UPDATED ATTEMPT; As per suggestion, I tried this...

TRUNCATE TABLE TEMP_PN_FORMAT;
INSERT INTO TEMP_PN_FORMAT
SELECT O.BASE_PART_CODE, 
O.MATERIAL_NUMBER, 
CASE WHEN ISNUMERIC([MATERIAL_NUMBER])=1 THEN CONVERT(VARCHAR(30),CAST([MATERIAL_NUMBER] AS FLOAT))
ELSE [MATERIAL_NUMBER] END AS PN_FORMAT
FROM [DCA-DB-326\MSBI_RS].[AGS_DATAMART].dbo.OPR_MATERIAL_DIM O

No error this time, but only smaller numbers are working. 647 worked as expected and was able to join, 4000192 did not.

enter image description here

You have data in the MATERIAL_NUMBER field that is not a number. Have you checked? Perhaps there is a dash (`-`)? — Rick S, Dec 16 '14 at 21:03
or perhaps a currency? http://technet.microsoft.com/en-us/library/ms186272%28v=sql.110%29.aspx — xQbert, Dec 16 '14 at 21:12
Yes, there is defiantly non-numerical data in material number, but shouldn't CASE ISNUMERIC evaluate these and only try to convert them leaving the rest as is? — Robert Pulido, Dec 17 '14 at 21:50

score 1 · Answer 1 · edited May 23 '17 at 10:26

You have some numeric data in the MATERIAL_NUMBER column that cannot be converted into an Integer.

The old code converted numeric data to a Double.

You might be able to fix the problem like this:

CASE WHEN ISNUMERIC([MATERIAL_NUMBER])=1 THEN CONVERT(VARCHAR(30),CAST([MATERIAL_NUMBER] AS FLOAT))
ELSE [MATERIAL_NUMBER] END AS PN_FORMAT

However, I wonder why you need to this. You have a column with text data. It must be text, or you wouldn't even need to check IsNumeric() You're ultimately converting this to... more text. Maybe you're trying to get a specific format, but that seems weird, too. Double is about as broad as it gets for numbers; you're not likely to change anything. More likely, this code was avoiding some issue in Access; an issue that might not even be present in Sql Server. Beyond that, this smells of poor database design in the first place. If you have mixed types like this a column, there's something wrong with the schema.

Based on your edit, this worked for me:

select cast(cast('000000000004000192' as int) as varchar(10))

----------
4000192

(1 row(s) affected)

You might also consider this answer:

Better techniques for trimming leading zeros in SQL Server?

Or, best of all, change the column type in the new table to be an integer.

I have updated my original question to provide more detail. In short yes, bad design decision. I am now try to recreate so I can revert. I will try as suggest as post back. — Robert Pulido, Dec 17 '14 at 22:01

score 0 · Accepted Answer · answered Dec 17 '14 at 22:48

I was able to solve this issue by using the following. I went from a thought process of trying to replicate means to replicate results.

TRUNCATE TABLE TEMP_PN_FORMAT;
INSERT INTO TEMP_PN_FORMAT
SELECT O.BASE_PART_CODE, 
O.MATERIAL_NUMBER, 
CASE WHEN ISNUMERIC(MATERIAL_NUMBER)=1 THEN SUBSTRING(MATERIAL_NUMBER, PATINDEX('%[^0 ]%', MATERIAL_NUMBER + ' '), LEN(MATERIAL_NUMBER)) 
ELSE MATERIAL_NUMBER END AS PN_FORMAT
FROM [DCA-DB-326\MSBI_RS].[AGS_DATAMART].dbo.OPR_MATERIAL_DIM O

While it works, SUBSTRING and PATINDEX are expensive functions to do on every row in the database. Joel's answer may perform better — SQLMason, Dec 18 '14 at 15:23

Convert MSAccess Query to TSQL

2 Answers2