How to select the data which have space end of string one or more

[wugon.net]

Env: DB2 V8 + FP13

Question: How to select the data which have space end of string one or
more, like 'AB ','AB '

Case:
..Create Test Table
db2 "create table test (vname varchar(100)) "

..Insert Test Data
db2 "insert into test values('AB'), ('AB '),('AB '),('AB ')"

..Select only have one space end of string 'AB' with follow sql, but
result not match our expectation
db2 "select * from test where vname='AB '"
VNAME
-----
AB
AB
AB
AB

now, our solution use follow sql
db2 "select * from test where length(vname)=3 and VNAME='AB '"

But, Anyone know another alternate way can select string like 'AB ' ?
Thanks

[Serge Rielau]

wu*******@gmail.com wrote:

Env: DB2 V8 + FP13

Question: How to select the data which have space end of string one or
more, like 'AB ','AB '

Case:
.Create Test Table
db2 "create table test (vname varchar(100)) "

.Insert Test Data
db2 "insert into test values('AB'), ('AB '),('AB '),('AB ')"

.Select only have one space end of string 'AB' with follow sql, but
result not match our expectation
db2 "select * from test where vname='AB '"
VNAME
-----
AB
AB
AB
AB

now, our solution use follow sql
db2 "select * from test where length(vname)=3 and VNAME='AB '"

But, Anyone know another alternate way can select string like 'AB ' ?

Try VNAME LIKE 'AB %'
No promises, but I think LIKE is special

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[wugon.net]

On Apr 21, 1:29 am, Serge Rielau <[e-mail]:

[e-mail] wrote:

Env: DB2 V8 + FP13

Question: How to select the data which have space end of string one or
more, like 'AB ','AB '

Case:
.Create Test Table
db2 "create table test (vname varchar(100)) "

.Insert Test Data
db2 "insert into test values('AB'), ('AB '),('AB '),('AB ')"

.Select only have one space end of string 'AB' with follow sql, but
result not match our expectation
db2 "select * from test where vname='AB '"
VNAME
-----
AB
AB
AB
AB

now, our solution use follow sql
db2 "select * from test where length(vname)=3 and VNAME='AB '"

But, Anyone know another alternate way can select string like 'AB ' ?

Try VNAME LIKE 'AB %'
No promises, but I think LIKE is special

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Hi Serge ,
Thanks your kindly advice, the LIKE syntax is work for our sql, Thanks

[Tonkuma]

VNAME LIKE 'AB %'
This will match with the value of VNAME (for example) 'AB CD'.
If your data doesn't include such value, Serge's answer will be
enough.

But, if your data include such value, my idea is
SELECT *
FROM test
WHERE vname = 'AB'
AND LENGTH(vname) 2;

More:
If consider more general case to find the values which doesn't care
leading characters and there are one or more trailing blanks.
For example:
select 'AB ', 'AB ', 'AB ', 'AB C ', 'KLM ', 'KLM ', 'VWX YZ ',
'VWX YZ ' from follwing data.

INSERT INTO test
VALUES 'AB', 'AB ', 'AB ', 'AB ', 'AB C', 'AB C ', 'AB CD'
, 'KLM', 'KLM ', 'KLM ', 'VWX YZ', 'VWX YZ ', 'VWX YZ ';

Try
SELECT *
FROM test
WHERE LENGTH(RTRIM(vname)) < LENGTH(vname);

[Serge Rielau]

Tonkuma wrote:

>VNAME LIKE 'AB %'

This will match with the value of VNAME (for example) 'AB CD'.
If your data doesn't include such value, Serge's answer will be
enough.

Oh dear. That's why they must call it a head-cold.
I shouldn't post when I'm sick ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ray]

On Apr 21, 6:23 pm, Serge Rielau <[e-mail]:

Tonkuma wrote:

VNAME LIKE 'AB %'

This will match with the value of VNAME (for example) 'AB CD'.
If your data doesn't include such value, Serge's answer will be
enough.

Oh dear. That's why they must call it a head-cold.
I shouldn't post when I'm sick ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

So how much longer will it be until regular expressions come to
SQL? :D

[Ian]

Ray wrote:

>
So how much longer will it be until regular expressions come to
SQL? :D

You mean like:

http://www-128.ibm.com/developerwork...301stolze.html

[Serge Rielau]

Ian wrote:

Ray wrote:

>>
So how much longer will it be until regular expressions come to
SQL? :D

You mean like:

http://www-128.ibm.com/developerwork...301stolze.html

I also have a regexp article half finished based on ICU.
Looking for a partner in crime to drive it to the finish line.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab