troubleshooting Question

Converting a varchar(4) to an integer in sql

Avatar of brgdotnet
brgdotnetFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
7 Comments5 Solutions381 ViewsLast Modified:
I have a table with several columns. One of the columns is named FITS_SUFFIX, and is of type VARCHAR(4). If I look at the data in that column using
a sql statement, I get a 4 digit string of alphabet characters, such as ABCW, BERU, KAPI, JMUU

Now what confuses me, is that in this sql stored procedure I am maintaining, they are converting this 4 digit character to an integer.
See  lines 3, 9, and 27 below. Then at line 34 they use this converted character value in an algorithm.

So what happens when you convert a character string like ABCW to an integer? Does it just convert the first character? I tried to do something like
that from the query analyzer command line, but kept getting errors. Anyway, I truly need expert help on this.

1 DECLARE CURS_STATE CURSOR
 2 FOR
 3    SELECT FITS_STATE_CD, FITS_PREFIX, FITS_SUFFIX
 4    FROM SMT_FCTS_FITS_STATE_CDS FITS
 5   WHERE FITS.FITS_ACTIVE = 1
 6   ORDER BY FITS_STATE_CD

 7 OPEN CURS_STATE
 8 FETCH NEXT FROM CURS_STATE
 9 INTO @lchStateCode, @lchPrefix, @lchSuffix

 10  WHILE @@FETCH_STATUS = 0
 11  BEGIN
 12     DECLARE CURS_EMP CURSOR
 13     FOR
 14     SELECT
 15     USTY.PSUS_USER_NUM AS PSUS_USER_NUM,
 16     USTY.FITS_STATE_CD AS FITS_STATE_CD,
 17     FITS.FITS_PREFIX AS FITS_PREFIX
 18     FROM SMT_FCTS_FUAD_FOOTETS_USR USTY,
 19     SMT_FCTS_FITS_STATE_CDS FITS
 20     WHERE USTY.FITS_STATE_CD = FITS.FITS_STATE_CD
 21     AND FITS.FITS_ACTIVE = 1
 22     AND USTY.FITS_STATE_CD = @lchStateCode
 23     AND (USTY.FUAD_ITS_CLERK_ID = '' OR USTY.FUAD_ITS_CLERK_ID IS NULL)
 24     AND USTY.PSTE_CODE = @lchProdSteCode
 25     ORDER BY USTY.PSUS_USER_NUM



 26     OPEN CURS_EMP
 27     SELECT @lchFITS_SUFFIX = CONVERT(INT, @lchSuffix)
 28     FETCH NEXT FROM CURS_EMP
 29     INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX

 30     WHILE @@FETCH_STATUS = 0
 31     BEGIN
 32        SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1

 33      UPDATE SMT_FCTS_FUAD_FOOTETS_USR SET
 34        FUAD_ITS_CLERK_ID = @lchFITS_PREFIX + (SELECT CHAR(65 +(@lchFITS_SUFFIX%17576) / 676)
 35       WHERE PSUS_USER_NUM = @lchPSUS_USER_NUM
 36       AND (FUAD_ITS_CLERK_ID = ' ' OR FUAD_ITS_CLERK_ID IS NULL


 37        FETCH NEXT FROM CURS_EMP
 38        INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX

 39       END

 40   CLOSE CURS_EMP
 41   DEALLOCATE CURS_EMP
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 5 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros