how to query only "179*" in mysql eg. "1780000001, 1790000001" > "1790000001"

Question

I want query data from Mysql query only "179%"

-

Data (separate |)

No|numberOfRecord

1792008328, 1840192095, 9170223798|1

1785005168, 1790192096, 9170223799|1

-

output want Data (separate |)

No|numberOfRecord

1792008328|1

1790192096|1

-

thank you for help me ^^

It is going to be tough in MySQL separating out individual numbers from a CSV list. `REGEXP` might let you target certain records, but that is all. I think the best answer here is to just normalize your data. — Tim Biegeleisen, Dec 13 '17 at 05:09
sorry No. is a ID(Sting) Finally data same Split"," + pivot in tableau program — Chaiyanun Bootnumpech, Dec 13 '17 at 06:44

score 2 · Answer 1 · answered Dec 13 '17 at 05:10

Assuming that column No has just 3 comma separated values, then one way could be to use SUBSTRING_INDEX to split the column No and then union all in inner query and then use like in outer query:

select * 
from
(
    select SUBSTRING_INDEX(t1.No, ',', 1) as No, numberOfRecord
    from table1 t1
    union all
    select trim(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.No,',',2),',',-1)), numberOfRecord
    from table1 t1
    union all
    select trim(SUBSTRING_INDEX(t1.No, ',', -1)), numberOfRecord
    from table1 t1
 ) t
where t.No like '179%';

Result

+------------+----------------+
|     No     | numberOfRecord |
+------------+----------------+
| 1792008328 |              1 |
| 1790192096 |              1 |
+------------+----------------+

DEMO

A clever and reasonable answer +1, but it assumes that the number of items in each CSV record is fixed. — Tim Biegeleisen, Dec 13 '17 at 05:12
thank you. if new data income - 1785005168, 1790192096, 9170223799, 9201234567|1 - how to solve it — Chaiyanun Bootnumpech, Dec 14 '17 at 02:59

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

The following is based on a "split string" approach found here which requires a table of numbers with one number per row. If you don't have such a table there are alternatives. such as just using a query like this:

SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ...

You need a series of numbers up to the maximum length of the string to be split. Once those numbers are available as rows then the comma separated string can be broken up into parts providing each part on a new row. Once that row structure exists it is a simple matter of using a where clause to remove the parts we are no longer interested in.

CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

CREATE TABLE yourtable (
  no VARCHAR(200),
  numberOfRecord INT
);

INSERT INTO yourtable VALUES
('1792008328, 1840192095, 9170223798',1),
('1785005168, 1790192096, 9170223799',1);

SELECT
       *
FROM (
      SELECT
            trim(SUBSTRING_INDEX(SUBSTRING_INDEX(t.no, ',', numbers.n), ',', -1)) no
          , numberOfRecord
      FROM numbers 
      INNER JOIN yourtable t ON CHAR_LENGTH(t.no)
           -CHAR_LENGTH(REPLACE(t.no, ',', ''))>=numbers.n-1
     ) d
WHERE no LIKE '179%'
ORDER BY no;

no         | numberOfRecord
:--------- | -------------:
1790192096 |              1
1792008328 |              1

as a standalone query:

      SELECT
            trim(SUBSTRING_INDEX(SUBSTRING_INDEX(t.no, ',', numbers.n), ',', -1)) no
          , numberOfRecord
      FROM numbers 
      INNER JOIN yourtable t ON CHAR_LENGTH(t.no)
           -CHAR_LENGTH(REPLACE(t.no, ',', ''))>=numbers.n-1

the "split string" does create many rows.

no         | numberOfRecord
:--------- | -------------:
1792008328 |              1
1785005168 |              1
1840192095 |              1
1790192096 |              1
9170223798 |              1
9170223799 |              1

dbfiddle here

score 0 · Answer 3 · answered Dec 13 '17 at 06:23

0

It should be pretty easy.

SELECT * from sometable
WHERE LEFT(NO,3) = '179'

Like Operators and such will work.

answered Dec 13 '17 at 06:23

Leptonator

3,379
2
38
51

how to query only "179*" in mysql eg. "1780000001, 1790000001" > "1790000001"

3 Answers3