Helpful Information

Category: Server side development

Shortening Data

If you have the data in "column X" = NNGS0182 is there a way to run a query to just pull characters 2-5. (ngs0) or maybe even create a stored procedure to in insert NNGS0182 as NGS0.

Thanks

The best way to do this is to probably use PHP before inserting teh data as you suggested. The code below will do that for you :)

$string = "NNGS0182";
$string = substr($string, 1, 4);

For more info see www.php.net/substr

Jee

You can do this using MYSQL

SELECT SUBSTRING('NNGS0182',2,4);

The layout is like this: SUBSTRING(str,pos,len)
You can find more about this at

http://www.mysql.com/doc/S/t/String_functions.html

Hope this helps
wabirdman

True, but its easy to do this before you enter, otherwise it has to be truncated each time the page loads, and if you're alking 100's of hits, then its ading to server load

Jee

Yes that is true. I normally use ORACLE, so I dont really worry that.

wabirdman

Originally posted by Jeewhizz
True, but its easy to do this before you enter, otherwise it has to be truncated each time the page loads, and if you're alking 100's of hits, then its ading to server load

That's only a valid concern if your assumptions about the use of that MySQL statement in context of the application is equal to what BillL thought of. In other words, only when you read out something for presentational display. Also, can you make sure that the "column X" is not used in other contexts where the full string of it's fields is needed? I mean, why should one devise it to store such a content if only a part if it would be actually needed.

I suspect that BillL might use it in an insert that relies on values retrieved by [..] JOIN statements. If that's the case, go with wabirdmans suggestion. So it's up to you BillL to clarify your question.