Converting Varchar to Int in a sub query

Question

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

is it like from '112345-6458PT' you want to extract '6458'?? — Unnie
– Unnie, Commented Mar 2, 2014 at 19:05
You don't need the EXISTS check. Just use SELECT TOP 1 @patientId = SUBSTRING. If no rows match no assignment is made. Also why no ORDER BY? — Martin Smith
– Martin Smith, Commented Mar 2, 2014 at 19:13

pas · Accepted Answer · 2014-03-02 19:50:13Z

1

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

answered Mar 2, 2014 at 19:50

pas

986 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting Varchar to Int in a sub query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related