>>Hi,
>>
>>I am troubleshooting the following issue. And I can't figure out what causes it.
>>
>>A somewhat long SQL Select has the following segment:
>>

>>REPLICATE(' ',10-LEN(ALLTRIM(REFERENCE)))
>>

>>
>>The purpose of the above is to create a string of entity codes (nbsp) equal to the number of spaces after some entry in a field. The field name is REFERENCE. It used to be up to 10 characters in length. And the user was able to enter only up to 10 characters. And the above code worked.
>>Then I increased the length of the field to 15. But the entry in the field is still the same, less or equal to 10 characters.
>>Now the above code does not work. The SQL Select returns the error:
>>

>>String is too long to fit.
>>

>>
>>If I change the 10 in the above REPLICATE with 15, no error.
>>

>>REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE)))
>>

>>
>>I don't understand why? Since the entry in the REFERENCE field is still only up 10 characters, the code 10-LEN... should work. Unless I miss something obvious.
>>
>>TIA for any explanation
>
>I decided that it is easier to run this SQL Select right from the VFP 9 command window, instead of running it from the ASP.NET app. And the result is the same, when running this SQL Select from the command window.
>Here is the entire SQL Select:
>

>select ALLTRIM(REFERENCE) + REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE))) + ' ' + REF_DESCR 
>AS WO_REFER1 FROM WO_REFER
>

>
>If the number in the SQL Select above is 10, I get error. If it is 15, no error. But,
>What is VERY strange to me, is when I can successfully SQL Select the query, the column WO_REFER1 is only about 60 to 62 char in length. It should be wider since the SQL Select concatinats the fields REFERENCE and REPLICATE(ENTITY) AND REF_DESCR field. So, basically only one or two letters from REF_DESCR go into the column WO_REFER1.

I simplified the SQL Select. Here is my shorter version:

SELECT REFERENCE, REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE))) + ' ' AS TEST FROM WO_REFER

The result: no matter what value is in the field REFERENCE, the value in TEST is just ONE string 'nbsp'. Why only one string for all rows??
If I change the above and replace 15 with 10, the error.

Maybe this will help someone to see the problem.

UPDATE: I removed the 'nbsp' from the expression above. And I get even more weird result.
Now TEST field get just one character

'&'

UPDATE 2:
It appears that the expression

REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE))) 

Looks at the length of the field REFERENCE and not content of it. How could it be?