Splitting String in SQL Query 2

[jontout]

Hi all.

I've written the following code to enable the storage of 2 values in one form field using a colon as the delimiter - rather than redesign the form which we don't have access to do.

select policy_key, substr(pol_notes,1, (instr(pol_notes, ':')-1)) netret, 
substr(pol_notes,(instr(pol_notes, ':')+1)) nri
from genpolicy where policy_key = 'XXXXX';

The form field stores the values 2.50:250.00 and the above code splits the string into 2 columns, my question is, on a relatively small amount of data, is this a wise move or would splitting the data at the front end (VB.NET) be a better idea?
This sql doesn't seem to impact on the dataset, and would save time rewriting the front end to accomodate this issue.

Cheers,

Jon

 

[simian336]

If I understand, this is a simple select statement.

There should be no noticable impact on the system. This is what the SQL server is designed to do.

Simi

 

[gmmastros]

I would suggest a minor change to your query:

select policy_key, substr(pol_notes,1, (instr(pol_notes [!]+ ':'[/!], ':')-1)) netret, 
substr(pol_notes,(instr(pol_notes[!]+ ':'[/!], ':')+1)) nri
from genpolicy where policy_key = 'XXXXX';

You see... when there is no colon in the data, you will get an error. With SQL Server, the error would be 'Invalid length parameter passed to the SUBSTRING function'. I'm not sure what database engine you are using, but I do strongly encourage you to check code against data that does not have a colon in it. The code I posted above will force the instr function to find the colon because we are hard coding it. If there is a colon in the data, instr will *likely* find the first one. When there is no colon in the data, instr will return the length of the string because of the hard coded colon at the end.

With the code I posted above, if the string is empty, both columns will return an empty string. If the column has NULL, both output columns will be NULL.

As for performance... the time required to do this is likely going to be the same whether it's done in the front end or in the database. For EXTREMELY busy databases, it is slightly better to do this in the front end so that the client computer is doing the extra processing instead of the server. If you are only ever doing this on a single row (or a small handful of rows) you're not likely to notice any difference.

Personally, I would keep this code in the database because if you ever decide to split the data in to 2 columns (like you should), then you would only need to change the table and the query without changing the front end code.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jontout]

Thanks both. The end user(s) are aware that they will need to use a colon or they won't get an output and they're ok with that.
I'm more used to doing this sort of thing within the front end, it seemed a bit more clunky to have it included in a sql statement, but it seems to run without any performance issues, so I'm happy with it.

Cheers,

Jon