To get digits after / in stored procedure

Question

hi guys, I have a field in table.Suppose its value is 2009/1234.I want to get digits before'/'.I want to do this in stored procedure in sql.Can anybody help?

Answer 1

Assuming you can guarantee the slash and digits will exist:

 SELECT SUBSTRING(myfield, CHARINDEX('/', myfield)+1, 1000) FROM mytable;

Answer 2

SUBSTRING(MyValue, CHARINDEX ('/', MyValue) + 1, 8000)

Answer 3

The right way to do this (in my opinion, of course, since "right" is a subjective term) would be to modify the schema so that these values are separated.

In other words, 2009 and 1234 would be in different columns.

It's almost always a bad idea, in terms of speed and scalability, to use per-row functions in your select statements. That's because the DBMS has to perform calculations on every single row it extracts, every time it extracts it.

If you're required to keep them as a single column, one trick I've used in the past is to use an insert/update trigger with two extra columns to receive the parts, so you would have:

SomeFields       |   Data    | PostSlash
-----------------+-----------+----------
blah, blah, blah | 2009/1234 | 1234

Yes, I know this isn't ideal 3NF but it's perfectly acceptable to break that for performance reasons if you do it right. The triggers will guarantee the columns always match and you'll find that your queries run a lot faster at the minimal expense of the trigger and the duplication of data.

But it's better to do that split once when a row is created or updated rather than every time you read it. That's because databases are usually read far more often than they're written. The trigger expense is amortized over all the selects.

Keep in mind that, if your table is small or (for some bizarre reason) written more often than read, you may be better off using per-row functions.

Answer 4

To get the digits before the slash, assuming the slash always exists:

SELECT LEFT(columnname, CHARINDEX('/', columnname, 1) - 1) FROM aTable