Retrieve specific string from column in SQL table

Question

Hi I have the following data column where the typical data will look like:

Row 1: RCS CARD: THANK YOU FOR YOUR PURCHASE AT PICK N PAY ON CARD ...1820 FOR R371.71 ON 14-03-2013 AT 09:46. AVAIL CREDIT R67. FOR QUERIES CALL 0861028889

Row 2: RCS CARD: THANK YOU FOR YOUR PURCHASE AT PICK N PAY ON CARD ...6825 FOR R3061.93 ON 14-03-2013 AT 09:45. AVAIL CREDIT R39. FOR QUERIES CALL 0861028889

I need to be able to extract the R371.71 and R3061.93 from row 1 and 2. What is the most accurate way to do this? Keeping in mind that R amount will change from row to row so a simple substring will not work?

Any advice would be extremely helpful.

Thanks, Jonathan

Do you want fetch those which have this kind of text pattern in the columns? — Ravinder Reddy
– Ravinder Reddy, Commented Apr 16, 2014 at 14:12
Is this MySQL or MS SQL Server? What version? (It matters, trust me :)) — Anthony Accioly
– Anthony Accioly, Commented Apr 16, 2014 at 14:13
better is read rows and to string-parsing work in application script rather than SQL — Grijesh Chauhan
– Grijesh Chauhan, Commented Apr 16, 2014 at 14:15

asafm · Accepted Answer · 2014-04-16 14:33:21Z

1

Well the proper way to do it is to use regexp in an external script/app since MySQL doesn't support regular expression sub strings.

If you do insist on using SQL the only way I could think of is by assuming that the string starts with:

RCS CARD: THANK YOU FOR YOUR PURCHASE AT PICK N PAY ON CARD

and just ignore that part. so the SQL should be:

SELECT SUBSTR(t, LOCATE('FOR', t, 61)+5 ,LOCATE('ON', t, 61)-1-LOCATE('FOR', t, 61)-5)
  FROM DATA

Again I would use regexp but you can see it's working in this SQLFiddle: http://sqlfiddle.com/#!2/966ad/7

answered Apr 16, 2014 at 14:33

asafm

9196 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravinder Reddy · Accepted Answer · 2014-04-16 14:44:22Z

0

If the column in concern has consistent text format as you mentioned in the question, then you can make use of substring_index, locate and substring functions to find the amount value.

select  
  -- column_name,  
  substring_index( substring( column_name,   
                              locate( 'FOR R', column_name, 1 ) 
                               + length( 'FOR R' ) 
                               - 1 
                   ), ' ', 1   
  ) as amount  
from table_name  
where   
  column_name like '%RCS CARD: THANK YOU FOR YOUR PURCHASE AT PICK N PAY ON CARD%';

Demo @ MySQL 5.5.32 Fiddle

If you want to extract only the amount without prefix 'R' then, remove the '-1' line from the above query.

edited Apr 16, 2014 at 14:44

answered Apr 16, 2014 at 14:39

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Collectives™ on Stack Overflow

Retrieve specific string from column in SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related