Variable substitution in MS Sql Substring() Function

[Rick Lipkin]

To All

I am writing a MS Sql Server Select statement and I need to use the Sql Substring() function .. Consifer this code as the first statement works, however the second does not due to the variable substitution ..
Any advice would be greatly appreciated.

Rick Lipkin

Code: Select all  Expand view

cSQL := "Select * from [Coproposal] where Substring(CINumber,1,3 ) = '"+ltrim(str(nProj))+".' Order by CINumber" // works  cinumber = '27.'
 

Code: Select all  Expand view

nLen := 3
cSQL := "Select * from [Coproposal] where Substring(CINumber,1,nLen ) = '"+ltrim(str(nProj))+".' Order by CINumber" // fails '27.' cinumber = '27.'
 

[Carlos Mora]

Rick,

In reference to the (non working) second sample, how do you insert the nLen value (3) in cSQL?.

May be using str with just 1 parameter can led to ambiguos results, so let me suggest to set them for ensuring the result.

An alternative approach could be to use LIKE instead of Substring

Code: Select all  Expand view

cSQL := "Select * from [Coproposal] where CINumber LIKE '"+ltrim(str(nProj))+".%' Order by CINumber"
 

[joseluisysturiz]

To All

I am writing a MS Sql Server Select statement and I need to use the Sql Substring() function .. Consifer this code as the first statement works, however the second does not due to the variable substitution ..
Any advice would be greatly appreciated.

Rick Lipkin

Code: Select all  Expand view

cSQL := "Select * from [Coproposal] where Substring(CINumber,1,3 ) = '"+ltrim(str(nProj))+".' Order by CINumber" // works  cinumber = '27.'
 

Code: Select all  Expand view

nLen := 3
cSQL := "Select * from [Coproposal] where Substring(CINumber,1,nLen ) = '"+ltrim(str(nProj))+".' Order by CINumber" // fails '27.' cinumber = '27.'
 

Intenta con SUBSTR(str FROM pos FOR len), saludos...

[Rick Lipkin]

Carlos

I originally used the Like % but, Like does a full table scan and can be brutal for our site people on VPN .. I was actually working from VPN last weekend and noticed the lag when the program got to that statement .. actually trying to do some Sql Tuning to improve performance.

Rick Lipkin

[Rick Lipkin]

To All

Using the same logic to concatenate in the Substring parameters as I did the nproj .. here is the working Sql Statement.

Thanks
Rick Lipkin

Code: Select all  Expand view

nLen := len(ltrim(str(nProj)))
nLen++

cSQL := "Select * from Coproposal where Substring(CINumber,1,"+ltrim(str(nLen))+" ) = '"+ltrim(str(nProj))+".' Order by CINumber"
 

[Carlos Mora]

Hi Rick,

I'm not an MS SQL Expert, i don't know if using functions in the WHERE part (like substring()) will be optimized by the server, i think it won't.
A different approch: Let's say CINumber values are <nproj>.<fix_len_number> like '12.00356', then the '12' project related records in coproposal will be those with CINumber >= '12.00000' and less than '12.99999', so

Code: Select all  Expand view

cProj:= ltrim(str(nProj))
cSQL := "Select * from Coproposal where CINumber >= '"+cProj+".00000' AND CINumber <= '"+cProj+".99999' Order by CINumber"
 

If there is an index on CINumber, this query will be absolutely optimizable, it should fly.
If the code is not numeric you can change the where limits to sth according to.

[Rick Lipkin]

Carlos

Thank you for your good suggestion .. unfortunitly I have inherited a system that has multiple relational tables and it goes something like this

Code: Select all  Expand view

Bitem Table         (pk) Project_number   '27'
Brevision Table     (fk) BINumber   '27.01', '27.02','27.03'     ... multiple line items concatenated by project_number and .01,.02,.03,04

Bitem Table         (pk) Project_Number  '271'
Brevision Table     (fk) BINumber  '271.01', '271.02','271.03'  ... multiple line items concatenated by project_number and .01,.02,.03,04
 

Project_Number is the primary key and BINumber is the foreign key .. where I got into trouble was using the Like% operator :
cSQL := "Select * from BRevision where BRNumber like '"+ltrim(str(nProj))+"%' Order by BINumber"

As you can see .. the like% operator would have returned both records and there are additional relational tree's from Brevision on other foreign key's in tables Corposal and the Citem table which uses the same root foreign key concatenated rule as root+.01,.02,.03, etc ..

The results I got did not seem different from the original program I am replacing .. all the totals seemed to add up, only when I looked at the oRs:RecordCount() did I realize I was returning a ton of rows at a horrible performance price. After reviewing the recordsets did I realize my error using Like% on the root and the wisdom of the origional designer by using a . to delineate the foreign key.

At that point I had no choice but to Substring() out my query and add the . to the Primary key in searching for the relational BINumber foreign key.. and the result was this :

Code: Select all  Expand view

nLen := len(ltrim(str(nProj)))
nLen++

cSql  := "Select * from BRevision where Substring(BRNumber,1,"+ltrim(str(nLen))+" ) = '"+ltrim(str(nProj))+".' Order by BINumber"
 

As far as performance, and the full table scan ... I have not tested it on a slow or VPN connection and I do not know if I will gain any better tuning, but I definitely will return less rows and an accurate relational result in the case of a primary key overlap.

Thanks
Rick Lipkin