RE: Better SQL performance than SUBSTRING -- MIDRANGE-L

Yes...you can create an index using a substring:

CREATE INDEX Table_Index
ON Table
(substr(Column,case when length(trim(Column))-7 <= 0
then 1
else length(trim(Column))-6 end , 7));

Generate a key using the last characters in the string no matter the total length of the string

Steve Needles

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Friday, May 04, 2018 9:21 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Better SQL performance than SUBSTRING

Well the DB Guru would say move the darn substring out into it's own column where God intended it to be.

I wonder if you can build an index over a substring. Of course the usual disclaimers about number of indexes and how often maintenance happens apply.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "Justin Taylor" <JUSTIN@xxxxxxxxxxxxx>
To: "MIDRANGE-L (midrange-l@xxxxxxxxxxxx)" <midrange-l@xxxxxxxxxxxx>
Date: 05/04/2018 10:16 AM
Subject: Better SQL performance than SUBSTRING
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I know that a LIKE performs better than a SUBSTRING in a SQL WHERE clause.
Is there a better option than a SUBSTRING for checking specific positions
in a column?

Given MY_TABLE with MY_COLUMN defined as char(12) and this query:
Select *
From MY_TABLE
Where substring(MY_COLUMN, 5, 6) = '123456';

Is there an alternative to SUBSTRING that will perform better?

TIA
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD

CONFIDENTIAL: The information contained in this email communication is confidential information intended only for the use of the addressee. Unauthorized use, disclosure or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by return email and destroy all copies of this communication, including all attachments.