INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Concatenate memo fields in SQL SELECT?

Concatenate memo fields in SQL SELECT?

(OP)
Hello Friends,

The following attempt to concatenate two memo fields in a SQL SELECT query fails, giving the error "String too long to fit. (Both memo fields are ordinary text.)

CODE

SELECT memofield1 + memofield2 AS bigmemo FROM temptbl_2 INTO dbf temptbl

Yet this work-around is successful:

CODE

SELECT memofield1, memofield2 FROM temptbl_2 INTO DBF temptbl
SELECT temptbl
REPLACE memofield2 WITH memofield1+memofield2 ALL

Is there a way to accomplish this all in the original SELECT SQL query?

Thanks

RE: Concatenate memo fields in SQL SELECT?

You need to specify the length of the target field. Try something like this:

CODE

SELECT ;
  PADR(LEFT(memofield1, 250) +LEFT( memofield2, 250), 500);
  AS bigmemo FROM temptbl_2 INTO dbf temptbl

Obviously, this will limit you to a given number of characters, which might not be want you want. But VFP has got to have some way of knowing how wide to make the field within the output table. Memos are variable length, so you can't just add two memos together and get the same width each time.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

RE: Concatenate memo fields in SQL SELECT?

(OP)
Thanks for your input, Mike.

Are you saying that VFP joins the 2 memo fields and places the result into a Character field of definite length (rather than simply making a third memo field, which I supposed would be variable in length)?

I was hoping there might be something like an SQL SELECT [MEMO] clause, akin to the GATHER [MEMO] command.

Thanks again.

RE: Concatenate memo fields in SQL SELECT?

Why not add them together afterwards?

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

RE: Concatenate memo fields in SQL SELECT?

If you're in VFP 9, use CAST:

SELECT CAST(Memo1 + Memo2 AS Memo) ...


Tamar

RE: Concatenate memo fields in SQL SELECT?

(OP)
Many thanks, Tamar. That's the kind of clue I was hoping for. (Yes, I'm in VFP9.)

I'm not familiar with CAST, but it works fine in this application, and I'm getting into it. One limitation is that apparently it doesn't permit the programmer to name the resulting field. The resulting field turns out to be (like it or not) "Expr_[n]", where n=the column position in the resulting table-- which is a usable, though not ideal, result.

Way cool!  Thanks again.

FOR GRIFF: Thanks for your response, Griff. But, as in my original question, an extra step is what I was trying to avoid. The "CAST" function suggested by Tamar is an elegant simplification.

RE: Concatenate memo fields in SQL SELECT?

Mike,

Your code is not going to work without CAST function suggested by Tamar.

RE: Concatenate memo fields in SQL SELECT?

BFWriter,

I hadn't thought of using CAST(). It's a better solution than the one I suggested. (I think mine will still work, but it creates a character field rather than a memo, which is not what you want.)

You wrote: One limitation is that apparently it doesn't permit the programmer to name the resulting field. You can solve that by using an AS clause:

CODE

SELECT CAST(Memo1 + Memo2 AS Memo) AS NewMemo

Mike

 

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business
 

RE: Concatenate memo fields in SQL SELECT?

(OP)
Thanks Mike. That works excellently. It's precisely the kind of thing I was hoping to find. (And, in addition, I came out learning a new VFP function I wasn't aware of.)

You guys are great!

Bill

RE: Concatenate memo fields in SQL SELECT?

As Mike said, it's not the job of the cast function to name the result, like no function names it's result, that's always done via "AS name".

The "AS" within CAST() is simply a special SQL syntax - as other databases have this too - to specify the result Type, but not the name.

Bye, Olaf.

RE: Concatenate memo fields in SQL SELECT?

Bill - you might find my e-book "Taming Visual FoxPro's SQL" useful. It's available from www.hentzenwerke.com, and it covers all of VFP's SQL commands.

Tamar

RE: Concatenate memo fields in SQL SELECT?

Mike,

You can not create a character field in VFP with the length of 500 characters. May be you meant 125 and 125 instead (the maximum length is 255 characters).

CREATE CURSOR ddd (ch C(100))
INSERT INTO ddd VALUES ('test')
SELECT PADR(REPLICATE('_',200) + REPLICATE('*',200),500) as LongField FROM ddd

--Error - String is too long to fit

SELECT CAST(PADR(REPLICATE('_',200) + REPLICATE('*',200),500) as M) as LongField FROM ddd && No error

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close