Error converting datatype varchar to bigint

Question

Post reply

Error converting datatype varchar to bigint

krypto69

SSChampion

Points: 13604

January 18, 2024 at 9:58 pm

#4364080

Hi

Tried a few things here but can't seem to get past the 'Error converting datatype varchar to bigint' error:

SELECT
DISTINCT
    provTaxID
   ,provNPI
   ,provName
   ,provShortName
   ,'https://nXXXXX/XXXXX/XXXX-view/' + provNPI BillProvURL_NPPES
   ,'https://www.google.com/search?q='
    + REPLACE( dbo.urlencode( provName , default), '%2B', '+' ) BillProvURL_Google
   ,ProvLookupSource
   ,provCategory
  ,format(CAST(provPhoneMain AS BIGINT), '###-###-####') provPhoneMain
  ,format(CAST(provPhoneFax AS BIGINT), '###-###-####') provPhoneFax
,provEmail
FROM
    dbo.calimtable
WHERE
    provType = 'BILL'

Wearing a hole in the wall where I am banging my head tried converting, replacing...missing it somehow.

January 29, 2024 at 11:09 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

krypto69 SSChampion Points: 13604 More actions · Answer 1

this works - but I loose the formatting


SELECT
DISTINCT
    provTaxID
   ,provNPI
   ,provName
   ,provShortName
   ,'https://npiregistry.cms.hhs.gov/registry/provider-view/' + provNPI BillProvURL_NPPES
   ,'https://www.google.com/search?q='
    + REPLACE( dbo.urlencode( provName , default), '%2B', '+' ) BillProvURL_Google
   ,ProvLookupSource
   ,provCategory
,CAST(provPhoneMain AS nvarchar) provPhoneMain
,CAST(provPhoneFax AS nvarchar) provPhoneFax
,provEmail
FROM
    dbo.tClaimTracker_Provider
WHERE
    provType = 'BILL'

ScottPletcher SSC Guru Points: 101076 More actions · Answer 2

What chars are in provPhoneMain and provPhoneFax? We don't have your data, so we have no idea what's in those columns.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18334 More actions · Answer 3

You have some values in the varchar columns provPhoneMain or provPhoneFax that cannot be cast as bigint.

You could try using TRY_CAST which will return NULL if it cannot be converted:

SELECT
    DISTINCT
    provTaxID,
    provNPI,
    provName,
    provShortName,
    'https://nXXXXX/XXXXX/XXXX-view/' + provNPI AS BillProvURL_NPPES,
    'https://www.google.com/search?q=' + REPLACE(dbo.urlencode(provName, default), '%2B', '+') AS BillProvURL_Google,
    ProvLookupSource,
    provCategory,
    TRY_CAST(provPhoneMain AS BIGINT) AS provPhoneMain,
    TRY_CAST(provPhoneFax AS BIGINT) AS provPhoneFax,
    provEmail
FROM
    dbo.calimtable
WHERE
    provType = 'BILL';

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver16

krypto69 SSChampion Points: 13604 More actions · Answer 4

Yeah I tried the try cast - didn't work.

I just removed the formatting - fine for now.

Thanks guys!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

The problem is that you are trying to format the results of a CAST to BIGINT. Instead of using FORMAT (bad idea - really slow) and trying to cast those to a numeric - you want to remove the non-numeric characters from the string, pad it (if needed) and then STUFF the dashes into the appropriate locations.

If you know what non-numeric characters exist - you can use REPLACE directly. If you don't, then you need a function that can return a cleaned string. Example using REPLACE:

SELECT
    DISTINCT
    provTaxID,
    provNPI,
    provName,
    provShortName,
    'https://nXXXXX/XXXXX/XXXX-view/' + provNPI AS BillProvURL_NPPES,
    'https://www.google.com/search?q=' + REPLACE(dbo.urlencode(provName, default), '%2B', '+') AS BillProvURL_Google,
    ProvLookupSource,
    provCategory,
    STUFF(RIGHT('0000000000' + REPLACE(provPhoneMain, '/', ''), 10), 7, 0, '-'), 4, 0, '-') AS provPhoneMain,
    STUFF(RIGHT('0000000000' + REPLACE(provPhoneFax, '/', ''), 10), 7, 0, '-'), 4, 0, '-') AS provPhoneFax,
    provEmail
FROM
    dbo.calimtable
WHERE
    provType = 'BILL';

What I would do is create an inline-table valued function to return a clean string - something like:

  CREATE Function [dbo].[fnCleanString] (
        @inputString varchar(8000)
      , @stringPattern varchar(50) = '[0-9a-zA-Z]'
        )
Returns Table
   With schemabinding
     As
 Return

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , iTally (n)
     As (
 Select Top (len(@inputString)) 
        checksum(row_number() over(Order By @@spid)) 
   From t t1, t t2, t t3                                -- 8000 rows
        )
 Select v.inputString
      , outputString = (Select substring(v.inputString, it.n, 1)
                          From iTally                     it
                         Where substring(v.inputString, it.n, 1) Like @stringPattern
                           For xml Path(''), Type).value('.', 'varchar(8000)')
   From (Values (@inputString)) As v(inputString);

And then use it like this:

SELECT
    DISTINCT
    provTaxID,
    provNPI,
    provName,
    provShortName,
    'https://nXXXXX/XXXXX/XXXX-view/' + provNPI AS BillProvURL_NPPES,
    'https://www.google.com/search?q=' + REPLACE(dbo.urlencode(provName, default), '%2B', '+') AS BillProvURL_Google,
    ProvLookupSource,
    provCategory,
    STUFF(RIGHT('0000000000' + pm.OutputString, 10), 7, 0, '-'), 4, 0, '-') AS provPhoneMain,
    STUFF(RIGHT('0000000000' + pf.OutputString, 10), 7, 0, '-'), 4, 0, '-') AS provPhoneFax,
    provEmail
FROM
    dbo.calimtable
CROSS APPLY
    dbo.fnCleanString(provPhoneMain, '[A-Z]') AS pm
CROSS APPLY
    dbo.fnCleanString(provPhoneFax, '[A-Z]') AS pf
WHERE
    provType = 'BILL';

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs