Deleting Characters fronm the Right

building reports desktopintelligence
1

Hi - I am trying to delete characters from the right

I have rows of names with their departments - it is the department names that I am trying to remove - I have tried Left and Right but no luck.

Length takes from the left but what takes from right

It must be something simple

PhilB (BOB member since 2008-02-28)

2

Hi,

Can you give us few examples of data in the column/variable and the required output?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks

Tony Smith Sales
Jerry Colhoughn Sales
Geraldine Boothroyd Sales

Its the Sales element that I want to remove

Cheers

PhilB (BOB member since 2008-02-28)

4

Can be donein two way.
Using a combination of Substr(), Pos(), and Len() functions.
Here in this scenario Replace function would be the best bet.

Name=Replace(<Name Object>,"Sales","")

KhoushikTTT :us: (BOB member since 2005-02-24)

5

Hi Phil,

This formula will extract everything from the beginning of the string until the second space found in the string:

=SubStr(<string> ,1 , Length(<string>) - Length(SubStr(SubStr(<string> ,Pos(<string> ," ")+1 , Length(<string>)) , Pos(SubStr(<string> ,Pos(<string> ," ")+1 , Length(<string>)) ," ") ,100)))

The formula can give incorrect results if there are people with 3 names. It means the name of a department is after the third space.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

Great used Replace and after a bit of tweaking with gaps worked a treat

Now is there anything that you can do if the Surname is before the First Name to swap them around, so it reads Tony Smith instead of Smith, Tony

PhilB (BOB member since 2008-02-28)

7

Hi,

You could use some creative combination of substr(), pos() and length() functions to swap names. However, it would make the swap in every row, regardless of whether a row has first name on the first place and then last name or vice versa.

It’s better to do such manipulation of strings in the database (on the query level) rather than on a report level.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

8

Hi,

As Marek Rightly said,

Its better to use substring() and pos() to achieve this. b’coz if you use

then a null will come in your name column which is not a good practice to have in DW project.
As your concept of data cleansing fails there. so better use what marek has written.
Refer to the link below for more info on these functions:

These links are from Web I but the functions mentioned here works in DeskI also.

I hope this helps.

Cheers,

shiva.tomar (BOB member since 2007-10-05)

9

[quote:720b7c498c=“shiva.tomar”]Hi,

Its better to use substring() and pos() to achieve this. b’coz if you use

then a null will come in your name column which is not a good practice to have in DW project.
As your concept of data cleansing fails there.
Cheers,
[/quote]

I would humbly disagree with your statement.
The Replace() function will identify for the text pointed and replace the identified text if found else the function would not make any effect on the data.

KhoushikTTT :us: (BOB member since 2005-02-24)

10

Dear NewyorkBaasha,

Thanks for the correction. :slight_smile:

There is another solution to this… (for this particular scenario) where the “Sales” is a suffix to each name in the column.

=rtrim(;-Number of characters you want to remove)
in this scenario where the number of characters are fixed. For Sales = 5
so the formula becomes…
=rtrim((;-5)

Cheers,

shiva.tomar (BOB member since 2007-10-05)

11

[quote:2d47092e2c=“shiva.tomar”]Rtrim()
[/quote]

Would had gone for that option if it had been just the text “Sales”
But the intial request criteria was department name. Hence I called for a replace() function. Again the replace() function didnt directly gave a soultion. As per PhilB, user did tweaking before getting what the final solution should be.

KhoushikTTT :us: (BOB member since 2005-02-24)

12

Yes, Thats why I wrote

And the above reply was a response to

This can still be done using rtrim() …
for this create a variable which can calculate the length of the string to be removed. (say Var 1)
then user this in another variable Var 2 = rtrim([Name object];-[var1])

But still for this problem I feel Your suggestion of using replace fits the best. As it needs les effort and gives better performance. :+1:

Cheers

shiva.tomar (BOB member since 2007-10-05)

13

Im not clear with your quotings :flush:
but I just went with PhilB virtual data… :nopity:

KhoushikTTT :us: (BOB member since 2005-02-24)