CODEWITHSUNDEEP
sql-server
Naveen
Naveen

Reputation: 701

Converting Varchar to Int in a sub query

I have a query some thing like this

DECLARE @patientId INT

SET @patientId = 0

IF EXISTS(SELECT TOP 1 SUBSTRING(TRN02, 1, CHARINDEX('-', TRN02) - 1)
          FROM   EDI_X12.dbo.X12_TRN
          WHERE  X12_Interchange_GUID = 'd6803485-3f46-485c-8288-2cfc98ec7088'
                 AND TRN02 LIKE '%-%')) 
BEGIN 
SET @patientId = (SELECT TOP 1 SUBSTRING(TRN02, 1, CHARINDEX('-', TRN02) - 1)
                  FROM   EDI_X12.dbo.X12_TRN
                  WHERE  X12_Interchange_GUID = @px12_interchange_guid
                         AND TRN02 LIKE '%-%') 
END

The value for TRN02 is 112345-6458PT Here '6457PT' is patientID the column is defined as INT and how do i convert varchar to int in this particular query?

I want to save '6457PT' as patientid in INT field

Thanks in advance

Naveen

Upvotes: 0

Views: 1063

Answers (1)

pas
pas

Reputation: 98

Try this

DECLARE @patientId int;

SELECT @patientId = CAST(SUBSTRING(TRN02, 1, CHARINDEX('-', TRN02) - 1) as INT)
      FROM   EDI_X12.dbo.X12_TRN
      WHERE  X12_Interchange_GUID = 'd6803485-3f46-485c-8288-2cfc98ec7088'
             AND TRN02 LIKE '%-%'

IF @patientId is null
BEGIN
     SET @patientId = 0;
END

In this way you have only one select instaed two( you don't need to check if the row exist). But you cannot cast the value 6458PT to INT, did you mean 6458 or 112345?

I hope this can help you

Upvotes: 1

Related Questions