mysql SUBSTR() problem

Question

Nice easy one for this morning.

Ok, here's my little sql statement

SELECT SUBSTR(quote,1,20) FROM b_quotes WHERE id='74'

This is returning an empty result which is confusing because if I call upon any other part of that record (the customers email address for example) it returns it perfectly. I've tried variations and it always seems to be the SUBSTR part that is failing.

could anyone shed some light on this?

Thanks Shane

Could you show us some sample data from: SELECT quote, SUBSTR(quote,1,20) FROM b_quotes WHERE id='74'? — a'r
– a'r, Commented Feb 19, 2011 at 8:13
@a'r Using the code you just posted, I get the entire sting as a result, not what I'd like (the first 20) — shane
– shane, Commented Feb 19, 2011 at 8:16
Try SELECT SUBSTR(quote,1,20) AS q FROM b_quotes WHERE id='74' — xzyfer
– xzyfer, Commented Feb 19, 2011 at 8:20
Does the following work: SELECT SUBSTR(quote,1,LEN(quote)) FROM b_quotes WHERE id='74' ??? — Phil Helix
– Phil Helix, Commented Feb 19, 2011 at 8:20
@xzyfer Thanks, this works perfectly. And to everyone else, thanks also for your time. — shane
– shane, Commented Feb 19, 2011 at 8:33

Brian Showalter · Accepted Answer · 2011-02-19 13:55:44Z

1

Since you haven't indicated which data type is used for the quote column, try this:

SELECT SUBSTR(CAST(quote as CHAR),1,20) FROM b_quotes WHERE id='74'

answered Feb 19, 2011 at 13:55

Brian Showalter

4,3572 gold badges29 silver badges30 bronze badges

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

Comments

xzyfer · Accepted Answer · 2011-02-19 08:50:39Z

0

Try SELECT SUBSTR(quote,1,20) AS q FROM b_quotes WHERE id='74'

answered Feb 19, 2011 at 8:50

xzyfer

14.2k5 gold badges37 silver badges46 bronze badges

2 Comments

Alessandro Over a year ago

Actually, while a reader passing by might think that there's a need to add a column alias name, this statement is totally equivalent to the one posted in the question, which is already correct. I believe that this works for @shane because of the way he/she is accessing the data returned from database. So the error is probably in the application code and NOT in the SQL statement.

shane Over a year ago

@Alessandro - That's exactly what's happening. I'm accessing the information in this manner $value['q'] whilst in a foreach loop

Alessandro · Accepted Answer · 2011-02-19 08:57:14Z

0

What's the datatype of the quote column? If it's a CHAR or VARCHAR, what's its length? What code are you using to access the data returned from the database?

Your SQL statement is correct, so if you want to avoid workarounds and just want to know why your query doesn't seem to work (as you asked) you have to research problems in your application code.

answered Feb 19, 2011 at 8:57

Alessandro

1,3369 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql SUBSTR() problem

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related