SQL query format help

[cscott]

Hi,

     I need to refine an SQL query and have not been able to find syntax that appears to work.

Use Case:

In a workflow an admin selects a controller name from a drop-down

     mysite1nodeA

     mysite1nodeB

     mysite2nodeA

     mysite2nodeB

Farther down in the workflow the admin must select a dataset and I need to refine it as too many volumes get put in the first returned.

For any given frame I will have two possible datasets, i.e.:

     mysite1nodeA_7_35

     mysite1nodeB_7_35

     mysite2nodeA_7_35

     mysite2nodeB_7_35

     mysite1node_0_35

     mysite2node_0_35

So selecting mysite1nodeA should be able to return - mysite1nodeA_7_35 or mysite1node_0_35

using this I can get mysite1node as part of the query, but I cannot determine where(or if possible) to place the wild card to match the rest of the dataset name, so I get no result.  In lab testing, creating a dataset named mysite1node returns, so the query as it stands is valid, just not what I need.

SELECT

    DISTINCT dataset.name

FROM

    storage.dataset

WHERE

    dataset.name like substr('${src_array}',1,LENGTH('${src_array}')-1)  <-- returns the controller name minus the A or B, but I have not been able to place the required "%" that will actually match the dataset.

    AND dataset.name NOT LIKE '%_drive'

    AND dataset.name NOT LIKE 'SnapMirrorLag'

    AND dataset.dfm_name LIKE '${pm_dfm}'

ORDER BY

    dataset.name

Any ideas for something other than my very basic SQL query capabilities would be greatly appreciated,

-Scott

[cscott]

Ok, this is not really what I want and probably not efficient, but it greatly reduces the number of datasets.  I would still welcome a better way to do it.

	AND dataset.name LIKE concat(trim(trailing 'a' from '${src_array}'),'%')
	OR dataset.name like concat(trim(trailing 'b' from '${src_array}'),'%')

[cscott]

Does what I need, for those who are unaware "_" is a special character for the SQL query, I had to escape it with "\"

AND dataset.name LIKE concat(trim(trailing 'a' from '${src_array}'),'\_%')

OR dataset.name LIKE concat(trim(trailing 'b' from '${src_array}'),'\_%')

OR dataset.name LIKE '${src_array}%'

Still have to believe there is a better way though.

- Scott