Oracle SQL how to use between in string

The problem is that your numbers are strings and the sort order for strings is different then those for numbers. If every sku starts with the string sku it would have been a better design to not store that prefix at all and store the SKU number as a proper number in the database.

You can use something like this:

select *
from the_table
where sku like 'sku%' -- only those that start with the prefix
  and to_number(regexp_replace(sku, '[^0-9]', '')) between 1 and 5

The regexp_replace() extracts only digits from the string, then converts that to a proper number which can be compared correctly using the between operator.

If the below sample is your data as you said earlier:
here are the example data i got: SKU4 SKU5 SKU10 SKU11 SKU12 SKU1 SKU2 SKU3 SKU150101 SKU15010601 SKU4 SKU1 SKU2 SKU1 SKU2 SKU3 SKU20164 SKU20165 SKU20166 SKU20167 SKU20168 SKU20169..i want only sku1,sku2,sku3,sku4,sku5

Then try below query:

select * 
from (
  Select * 
  from table_name 
  order by column_name aesc
) 
fetch first 5 rows only;

For string operation you want to use exact scenario with text case sensitive. Can you try the below code

Method 1:

Select * from table_name where column_name like 'sku_'

Method 2:

Select * from table_name where column_name in ('sku1','sku2','sku3','sku4','sku5')

Can't you just get the numbers from the string and use it in the BETWEEN clause ? Considering it always starts with 3 letters :

Ex :

CREATE TABLE TEST (
  ID NUMBER PRIMARY KEY,
  TEXT VARCHAR(255)
);

INSERT INTO TEST(ID, TEXT) VALUES(1, 'sku1');
INSERT INTO TEST(ID, TEXT) VALUES(2, 'sku2');
INSERT INTO TEST(ID, TEXT) VALUES(3, 'sku3');
INSERT INTO TEST(ID, TEXT) VALUES(4, 'sku4');
INSERT INTO TEST(ID, TEXT) VALUES(5, 'sku23');
INSERT INTO TEST(ID, TEXT) VALUES(6, 'sku40');
INSERT INTO TEST(ID, TEXT) VALUES(7, 'sku123');

Then :

SELECT *
FROM TEST
WHERE TO_NUMBER(SUBSTR(TEXT, 4)) BETWEEN 3 AND 30;

Result :

|--------------|--------------|
|      ID      |     TEXT     |
|--------------|--------------|
|      3       |     sku3     |
|      4       |     sku4     |
|      5       |     sku23    |
|--------------|--------------|

Or of course if you need the strings in the BETWEEN clause :

SELECT *
FROM TEST
WHERE TO_NUMBER(SUBSTR(TEXT, 4)) BETWEEN TO_NUMBER(SUBSTR('sku3', 4)) AND TO_NUMBER(SUBSTR('sku30', 4))

Try the fiddle.

EDIT : According to the comment, if you need to get the last digits from the string, you can use the following (uses REGEXP_SUBSTR function and a regexp to extract the digits from the end of the string) :

SELECT *
FROM TEST
WHERE TO_NUMBER(REGEXP_SUBSTR(TEXT,'\d+$')) BETWEEN 3 AND 30;

\d+ matches a group of digits. $ matches end of line.

New fiddle.