I want to parse data from one field into two based upon criteria.

Tags:
Parsing
SQL queries
SQL Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

What database are you using?
This could be slightly different from one RDBMS to another.

In oracle you could use the INSTR and SUBSTR functions, and in T-SQL (Sql server) you would use CHARINDEX and SUBSTRING.

For example, the following query would show the separated fields (Oracle):

select substr(field_x,1,instr(field_x,'*')-1) first_field,
substr(field_x, instr(field_x,'*',-1)+1) second_feld
from your_table;

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following