9 Mar 2012

Parsing a Set in SQL

I was trying to parse the data from the common platform enumeration which is a big dictionary of all or most of commercially or freely available applications, operating systems and hardware (the hardware part I didn't really understand).

Here is an example for "Microsoft Access 2000 sp2"
cpe:/a:microsoft:access:2000:sp2

As you can see, the data is separated by colons.
To get the parts that I want in that string, I used the following SQL statement:

*edit from Scot's comment*
mysql> set @v="cpe:/a:microsoft:access:2000:sp2";
Query OK, 0 rows affected (0.00 sec)
mysql> set @p=2 /*the part I want */;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);  |
+---------------------------------------------------------------------+
| /a                                                                  |
+---------------------------------------------------------------------+
 
mysql> set @p=4;
Query OK, 0 rows affected (0.00 sec)
 
mysql>  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| reverse(SUBSTRING_INDEX(reverse(SUBSTRING_INDEX(@v,':',@p)),':',1)) |
+---------------------------------------------------------------------+
| access                                                              |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)


Hope someone else finds this useful..



2 comments:

  1. Cut the two REVERSE calls and negate the 1:
    SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1)

    ReplyDelete
  2. Right you are.
    It's even in the official documentation like that.
    Thanks for the advice.

    ReplyDelete