Search code examples
sqloledbvisual-foxprodbase

Why would SQL truncate the right-side of a string when using the right function?

I have a database with MANY out-of-date file locations. The difference between the out-of-date file locations and the correct locations is simply the left-side of the address. So, I am attempting to take the left-side off and replace it with the correct string. But, I can't get there because my query is altering the right-side of the address.

This query is made using "vfpoledb."

SELECT RIGHT(LINK,LEN(LINK)-8) ,LEN(LINK)-8,RIGHT(LINK,77),LINK 
FROM LINKSTORE 
WHERE DOCLBL = "V46145002A"

This query returns the following:

EXP1:

\SHARES\DATA\QMS\QMS DATA\TRACKING FILES\REMOTEENTRIES\V46145 216447

EXP2:

 77

EXP3:

\SHARES\DATA\QMS\QMS DATA\TRACKING FILES\REMOTEENTRIES\V46145 216447-002A.PDF

LINK:

\\SERVER\SHARES\DATA\QMS\QMS DATA\TRACKING FILES\REMOTEENTRIES\V46145 216447-002A.PDF

I don't understand why EXP1 and EXP3 are giving different results. EXP3 is what I'm looking for EXP1 to return. If I could get that, I could append the correct left-hand-side and create an update query to fix everything.

Edit:

Even when changing the query to:

SELECT RIGHT(LINK,LEN(LINK)) ,LEN(LINK)-8,RIGHT(LINK,77),LINK 
FROM LINKSTORE 
WHERE DOCLBL = "V46145002A"

The link still cuts off at the same point, which is odd because expression_3 which still uses Right(), but manually provides the length instead of using Len() does not do this.

Furthermore, it seems that when I run the query to include all results:

SELECT RIGHT(LINK,LEN(LINK)) ,LEN(LINK)-8,RIGHT(LINK,77),LINK 
FROM LINKSTORE 
WHERE 1=1

All values returned by Exp1 are equal in length even though Exp2 and Link are different in size.

So back to the problem, how can I run a query to replace the left-side with the correct server if I can't separate them out?

Solution

  • OK this is tricky, I did some Foxpro 20 years ago but don't have it to hand.

    Your SELECT statement looks OK to me. In the comments under the question Thomas G created this DbFiddle which shows that in a 'normal' dbms, your SELECT statement gives the result you are expecting: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=37047d2b7efb91aaa029fa0fb98eea24

    So the problem must be something FoxPro/dBase specific rather than a problem with your SELECT statement.

    Reading up I see people say that with FoxPro always use ALLTRIM() when using RIGHT() or LEN() on table fields because the data gets returned padded with spaces. I don't see how that would cause the exact bug you're seeing but you could try this maybe:

    SELECT RIGHT(ALLTRIM(LINK),LEN(ALLTRIM(LINK))-8) ,LEN(ALLTRIM(LINK))-8,RIGHT(ALLTRIM(LINK),77),ALLTRIM(LINK)
FROM LINKSTORE 
WHERE DOCLBL = "V46145002A"

    edit: OK I got a better idea - are there other rows in your result set?

    According to this: https://www.tek-tips.com/viewthread.cfm?qid=1706948 ... when you do SELECT (expr) in FoxPro whatever the length of the expr in the first row becomes that max length for that 'field' and so all subsequent rows get truncated to that length. Makes sense in a crazy 1970s sort of way.

    So perhaps you have a row of data above the one we are talking about which comes out at 68 chars long and so every subsequent value gets truncated to that length.

    The way around it is to pad your expression results with CAST or PADR:

    SELECT PADR(RIGHT(ALLTRIM(LINK),LEN(ALLTRIM(LINK))-8),100),LEN(ALLTRIM(LINK))-8,PADR(RIGHT(ALLTRIM(LINK),77),100),LINK
    FROM LINKSTORE 
    WHERE DOCLBL = "V46145002A"

    Or same without the ALLTRIM()

    SELECT PADR(RIGHT(LINK,LEN(LINK)-8),100),LEN(LINK)-8,PADR(RIGHT(LINK,77),100),LINK
    FROM LINKSTORE 
    WHERE DOCLBL = "V46145002A"