CODEWITHSUNDEEP
sqlsql-server-2008t-sql
huMpty duMpty
huMpty duMpty

Reputation: 14460

Remove a full stop from the end of data

This is something similar to this question about How to remove a full stop from the end of data

But in this case the data field can be like this

MyVal 

 abcd.          ->>expected   abcd
 abcd..                       abcd
 abcd .                       abcd 
 abcd ..                      abcd
 ab.abb...                    ab.abb

etc....

I can do something like 

Select case when like '%.' then substring(MyVal ,1,len(MyVal )-1)
       case when like '%..' then substring(MyVal ,1,len(MyVal )-2)

by matching every kind of pattern there going to be.. Since this is manual way, I am looking for a general way that can remove any number of full stop may occur after the value field.

Thanks

Upvotes: 0

Views: 3298

Answers (5)

John Dewey
John Dewey

Reputation: 7093

You could try this approach:

DECLARE @t TABLE(somestrings VARCHAR(20))
INSERT INTO @t VALUES('abcd.')
, ('abcd..')
, ('abcd .')
, ('abcd ..')
, ('ab.abb...');
SELECT somestrings=LEFT(somestrings,LEN(RTRIM(REPLACE(somestrings,'.', ' ')))) FROM @t;

Result:

somestrings
--------------------
abcd
abcd
abcd
abcd
ab.abb

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select substring(MyVal, 1, len(MyVal)+1-patindex('%[^.]%', reverse(MyVal)))

Update:
If you want to remove spaces as well you can use this instead.

select substring(MyVal, 1, len(MyVal)+1-patindex('%[^. ]%', reverse(MyVal)))

SE-Data

Upvotes: 5

Tim Schmelter
Tim Schmelter

Reputation: 460138

This should work:

SELECT CASE WHEN CHARINDEX('.', MyVal) > 0 
        THEN RTRIM(LEFT(MyVal, CHARINDEX('.', MyVal) - 1))
        ELSE MyVal 
       END  As NewValue
FROM Table

Tested with:

declare @t1 table(MyVal varchar(20));
insert into @t1 values('abcd.');
insert into @t1 values('abcd..');
insert into @t1 values('abcd .');
insert into @t1 values('abcd ..');
insert into @t1 values('abcd   . ..');
insert into @t1 values('abcd... . ..');

SELECT   MyVal As FullStopVal
      ,  CASE WHEN CHARINDEX('.', MyVal) > 0 
          THEN RTRIM(LEFT(MyVal, CHARINDEX('.', MyVal) - 1))
          ELSE MyVal  END  As NewValue
FROM @t1

EDIT: My approach doesn't like points in the middle of the value as @MikaelEriksson has already noted, but it works for values like 

abcd   . .. 
abcd       ... . ..
abcd... . ..

unlike his :)

Upvotes: 0

sgmoore
sgmoore

Reputation: 16067

You can create a function like

Create FUNCTION [dbo].[fn_RemoveFullStopsFromEnd] (@Input varchar(255))
Returns varchar(255)
AS
BEGIN
    DECLARE @Output VARCHAR(255) 
    SELECT @OutPut = @Input

    While (Len(@Output) > 0) and (SUBSTRING(@Output,Len(@Output),1) = '.')
    Begin
       SELECT @Output = substring(@Output ,1,len(@Output )-1)
    End
    RETURN @Output
END

Usage

 select dbo.fn_RemoveFullStopsFromEnd('abcd..')

Can easily be amended if you want to remove the spaces as well as the fullstops from the end.

Upvotes: 0

weenoid
weenoid

Reputation: 1186

I'm not sure if this is possible without using T-SQL:

DECLARE @Example varchar(max) = 'Hello, Dave....'

SET @Example = LTRIM(RTRIM(@Example))

WHILE SUBSTRING(REVERSE(@Example), 0, 2) = '.'
BEGIN
    SET @Example = SUBSTRING(@Example, 0, LEN(@Example) - 1)
END

SELECT @Example

Upvotes: 1

Related Questions