Open SQL - fetch selective records whose field value is 5 characters

[Former Member]

Hi,

I am using open SQL.

I have a table which has a field with max length of 10 characters. Now I want to fetch only those records whose value length is 5 characters.

Can I achieve this without using wildcard '_' ?

Is there a way by which I can find the length of the field value?

Thanks.

[Former Member]

Hi,

I am using open SQL.

I have a table which has a field with max length of 10 characters. Now I want to fetch only those records whose value length is 5 characters.

Can I achieve this without using wildcard '_' ?

Is there a way by which I can find the length of the field value?

Thanks.

[Former Member]

Hello,

Explain your problem little bit clearly.

If possible give one example.

[Former Member]

Hello Santosh,

If a field lendth say MATNR is 18 characters and in MARA table I have material nos. as

10010001,

20010002,

30010003,

400100045.

Now in my SQL query, I would like to fetch only the first three material nos., ie, all material nos whose length is 8 digits.

Is there a way to derive it in SQL where condition (not using '_' wildcard).

Thanks.

[ThomasZloch]

WHERE MATNR LIKE '________' would work, why don't you want to use wildcards?

Thomas

Edit: actually it would not work if MATNR contains only digits, since these are stored with leading zeroes in the DB. Next one please.

[Former Member]

Hi Thomas,

I agree that this will not work for MATNR. My requirement is not for MATNR.

The reason for me not to use '_' wildcard is that I want to do this for 1100, 2000, 3000 .... 7000 series of a particular field. Using '_' would mean that i will have to use '_' for all the series.

In native SQL I can use 'where LEN(VKBUR) = 3'.

Is there a similar way in open SQL?

Thanks.

[Former Member]

Hi,

I think we can do like this.

Initially fetch all the data in to the internal table.

then we can delete the records whose length greater than 5 and less than 5.

Regards.

[ThomasZloch]

> In native SQL I can use 'where LEN(VKBUR) = 3'.

>

> Is there a similar way in open SQL?

I don't think so.

> I agree that this will not work for MATNR. My requirement is not for MATNR.

Right, you mentioned field length 10...my mistake.

> The reason for me not to use '_' wildcard is that I want to do this for 1100, 2000, 3000 .... 7000 series of a particular field. Using '_' would mean that i will have to use '_' for all the series.

Not completely getting this, so I'll withdraw now

Thomas

[Clemenss]

Hey,

why not use open sql and put +++++ in your select-options?

This will return all values consisting of 5 non-blank characters - which may have spaces enclosed.

IE SE16 for T000 with ++++++ in field MTEXT will select SAP AG but probably not any other client because usually the text is longer.

Regards,

Clemens.

[ThomasZloch]

This would be the same as LIKE '_____', but he doesn't like it...

[Clemenss]

Hi kumar,

possibly you can put it into a select-option range LOW = 1100 ...to HIGH = 9000.

Regards,

Clemens

[Former Member]

Hi

Good

I dont think you can give the field range while selecting a particular field data from the table, so you will get complete filed value with the availble length. If you truncate some the digits outside the select statement.

Thanks

mrutyun^

[Former Member]

All answers to this question are known. My question is not understood.

Thanks for your time and effort.