selecting from a string

[sarahw]

I need to remove the last 10 characters from a string and then display the remaining characters e.g if the string value is area-centre-servers, I wish to display only area-centre.

I think that I have an idea how to do this, but cannot seem to get the syntax right. Please see the code below where area = varchar (64)

select distinct area,substring(area,1,areadisplay),count(*)as servertotal
(select len(area)-8 as areadisplay from unit)
from unit
where area like '%server%'
group by area

Any help is much appreciated

 

[grega]

Almost there ...
[tt]
select distinct area, substring(area, 1, len(area)-10), count(*)as servertotal
from unit
where area like '%server%'
group by area
[/tt]

.. should do the trick (depending on the syntax of substring and len on your DB server.

Greg.

 

[sarahw]

Thanks Greg - works a treat

Sarah

 

[stormcloudgathering]

i have a similar problem in that I want to ditch the last 6 characters from a string.
However I'm doing this in a product called Cognos Transformer and it has an inbuilt SQL generator thingy and it won't allow the "len" command
Any ideas how I can get round that?
thanks
lynne

 

[stoggers]

Hi,


A useful technique where you need to introduce special processing in environments where SQL generators or similar tools are involved is to create a view on the base table and incorporate the manipulation in the tool.

In this instance you could select columns from the base table and then include a function call to strip out the extraneous characters.

Of course, this will only work if Cognos allows you to select from views as well as base tables.