CODEWITHSUNDEEP
mysqlsql
Dunkey
Dunkey

Reputation: 1922

mysql substring get only characters

I have records like these:

RCV0001
RCV0002
RTN0003
RTN0004
SLE0005
RCV0006

I want to query for records that start with 'RCV' only and display only records.

This is what I've tried so far:

select substring(documentnumber, 1)
LIKE '%RCV%'
from transactionheader

But I'm not getting my desired result. Any ideas? I'd gladly appreciate your help. Thanks.

Upvotes: 0

Views: 221

Answers (3)

spencer7593
spencer7593

Reputation: 108370

The expression in the select list of your query returns a boolean, so the query will only return 0, 1 or NULL for every row in the table.

SELECT SUBSTRING(documentnumber, 1) LIKE '%RCV%'
  FROM transactionheader

For every row in the table, the first character of documentnumber will be inspected to see if it contains the string 'RCV', which will never be true. The query is going to return 0 or NULL for every row.

There is more than one query that will return documentnumber that start with 'RCV'. Here is one example:

SELECT h.documentnumber
  FROM transactionheader h
 WHERE h.documentnumber LIKE 'RCV%'

The WHERE clause specifies the conditional tests that will be performed on each row, only rows that "satisfy" the predicate will be returned.

Upvotes: 1

Giacomo1968
Giacomo1968

Reputation: 26066

Your original query has no WHERE clause so everything is being selected. Also, I would recommend using REGEXP instead. Here is my rewritten example.

SELECT substring(documentnumber, 1)
FROM transaction header
WHERE documentnumber REGEXP '^RCV'
;

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23361

Will need to add a filter on the where statement

select documentnumber
from transactionheader
where  documentnumber  LIKE 'RCV%'

Upvotes: 4

Related Questions