Substring to First Space

[strom99]

I have an item description field char(100), I need to do the following

substr(item_desc,1, 1st Space)

How would I accomplish?

Thanks

 

[BillDHS]

substr(item_desc,1, (INDEX(item_desc, ' ')- 1)) put a space between trhe quotes if the INDEX function. INDEX returns and integer, subtract one to get the end of word.

If the item_desc field is empty, this -1 can cause out of range problems, so don't subtract it and leave a space on the end.

 

[tdgulbra]

This should give you the answer you are looking for:

substr(item_desc,1,(index,' '))

Tony

 

[teraable]

Is there a way to check for a char that occurs many times in the field? For example, I have a field with many '-' in it. I need to substr the data between the 4th and 5th '-'.

 

[tdgulbra]

Well, I could begin with a lecture on normalizing data before it goes into the tables, but I won't.

I can't think of a way to do this without some very complex SQL. It would be better to break it out in code than SQL.

Sorry,
Tony

 

[gprasad]

How to check for existence of a table(i.e table name) in a particular database and drop if it exists? Thanks in advance...

 

[tdgulbra]

This is a "quick and dirty" solution. I understand there is dynamic SQL possibilities in stored procedures, but I have not worked with them yet.

In bteq, try the following:

.set titledashes off;
.export report file=temp.sql;
select 'drop table '||trim(tvmname)||';' title ''
from dbc.tables
where databasename = 'mydbase'
and tablename = 'mytable'
and tablekind='t';
.export reset;
.if activitycount = 0 then .goto done;
database mydbase;
.run file=temp.sql;
.label done;

Hope this helps,
Tony

 

[gprasad]

Thanks a lot tdgulbra. Time being this will do.