Complicated LIKE Expression in derby (Java DB)

i've a table with ID, Name both are String type in ID i'v 开发者_开发知识库value like

1.3.6.1, 
1.3.6.2, 
1.3.6.1.2, 
1.3.6.1.3, 
1.3.6.1.4, 
1.3.6.2.1.
1.3.7.2,
1.3.7.5,
1.3.8.1,

etc

I need to retrieve records like 1.3.6. .. but not like 1.3.6.ANY_NUMBER. .., Can u help me to write a Derby query for it

Thanks in advance Hanks

You wrote the answer yourself

WHERE id LIKE '1.3.6%' AND id NOT LIKE '1.3.6.1%'

Maybe you better don't use LIKE but a simple equals:

SELECT * FROM db WHERE id='1.3.6';

Use this to select data from [column_name] in format up to 3 dots (like 1, 1.1, 1.1.1 but never 1.1.1.1)

SELECT SUBSTRING_INDEX([column_name],".",3) FROM [table_name];

or

Use this to select rows where [column_name] is not longer than 5 chars (1, 1.1, 1.1.1 but never 1.1.1.1)

SELECT [column_name] FROM [table_name] WHERE CHAR_LENGTH([column_name]) < 6