-2

I have mysql record as below

| id  | domain_id | name              | type | content      | ttl   | prio | change_date |

| 100 |        12 | www.testdomain.org | A    | 1.1.1.1     | 86400 |    0 |  1231243234 |

And I want to select from that table in the field of name only www without .testdomain.org

How can I select and get that result? Please help

regards,

Satya
  • 8,693
  • 5
  • 34
  • 55
Sokphak
  • 21
  • 1
  • 2
  • 5
  • 1. It looks like it will always be www, wouldn't it? 2. Leave such things to wherever you call the database from. – sashkello Jun 06 '13 at 04:32
  • possible duplicate of [Can Mysql Split a column?](http://stackoverflow.com/questions/1096679/can-mysql-split-a-column) – atk Jun 06 '13 at 04:34
  • 1
    dupe: http://stackoverflow.com/questions/1096679/can-mysql-split-a-column – atk Jun 06 '13 at 04:34

1 Answers1

2

UPDATED Based on comments

SELECT id, 
       domain_id, 
       LEFT(name, LENGTH(name) - LENGTH(SUBSTRING_INDEX(name, '.', -2)) - 1) name,
       type,
       content,
       ttl,
       prio,
       change_date
  FROM table1

Sample output:

|  ID | DOMAIN_ID |     NAME | TYPE | CONTENT |   TTL | PRIO | CHANGE_DATE |
----------------------------------------------------------------------------
| 100 |        12 |      www |    A | 1.1.1.1 | 86400 |    0 |  1231243234 |
| 101 |        12 |    www.a |    A | 1.1.1.1 | 86400 |    0 |  1231243234 |
| 102 |        12 | test.abc |    A | 1.1.1.1 | 86400 |    0 |  1231243234 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Suppose I have other record something like www.a.testdomain.org or test.abc.domain.net... And I want get only data before .testdomain.org and .domain.net – Sokphak Jun 06 '13 at 04:40