I want to parse data from one field into two based upon criteria.
I have lots of data with disparate information in one field and I need to take all information to the right of any * that might appear into a separate field? There will be an * about 10% of the time.
Examples of the field in question.
Example 1:
CKD Stage I ****250.40/585.1
Example 2:
/Prosthesis Ca *185
So in the above two examples, I would want a separate field for 250.40/585.1 in the first example, and a separate field for 185 in the second example. The original field in the first would then be CKD State I and in the second example, the original field would be replaced with /Prosthesis Ca
Any help?
Thanks!
John
Software/Hardware used:
Software/Hardware used:
ASKED:
December 4, 2008 4:21 PM
UPDATED:
December 4, 2008 7:38 PM
Answer Wiki:
What database are you using ?
This could be slightly different from one RDBMS to another.
In oracle you could use the <a href="http://www.techonthenet.com/oracle/functions/instr.php">INSTR</a> and <a href="http://www.techonthenet.com/oracle/functions/substr.php">SUBSTR</a> functions, and in T-SQL (Sql server) you would use <a href="http://msdn.microsoft.com/en-us/library/ms186323.aspx">CHARINDEX</a> and <a href="http://msdn.microsoft.com/en-us/library/aa259342(SQL.80).aspx">SUBSTRING</a>.
For example, the following query would show the separated fields (Oracle):
<pre>select substr(field_x,1,instr(field_x,'*')-1) first_field,
substr(field_x, instr(field_x,'*',-1)+1) second_feld
from your_table;</pre>
In Oracle it is easier to manage those records that have more than one ‘*’, because you can use a negative ‘start_position’ in the INSTR function, to start searching from the end of the string, which I think is not true with T-SQL.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
