Shortening Data

[BillL]

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

[Jeewhizz]

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

PHP Code:

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


For more info see www.php.net/substr

Jee

[wabirdman]

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

[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

Jee

[wabirdman]

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

wabirdman

[mordred]

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.