CODEWITHSUNDEEP
sql-servert-sql
Ibrahim Shaikh
Ibrahim Shaikh

Reputation: 398

T-Sql Remove all the dots but keep the last one

I have a variable in Sql Like the below

DECLARE @MyVariable AS VARCHAR(50) = 'My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa'

When I print @MyVariable 

print(@MyVariable)

The output is like below

My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa

which is fine but now I want to remove all the dots except the last dot

my expected output is looks like below

The output is like below

My Name Is Ibrahim AND I am Searching For A Solution. aa

How can I achieve this, I google but unable to find anything

Upvotes: 0

Views: 650

Answers (4)

beyt3i
beyt3i

Reputation: 31

Within three step, you can achieve the result you want. First variable finds location of last dot, Second variable remove dots except the last one, Third variable finds string after the last dot, and two variables are concatenated for desired output.

DECLARE @MyVariable AS VARCHAR(100) = 'My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa'
DECLARE @FirstDot as int =LEN(@MyVariable)-PATINDEX('%.%',REVERSE(@MyVariable))
DECLARE @RemoveDotsBeforeLastDot as varchar(100) =  REPLACE(SUBSTRING(@MyVariable,1,@FirstDot),'.',' ')
DECLARE @AfterLastDot as varchar(100) = SUBSTRING(@MyVariable,@FirstDot+1,LEN(@MyVariable))

select CONCAT(@RemoveDotsBeforeLastDot,@AfterLastDot)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

And yet another (simpler, IMHO) option:

DECLARE @MyVariable AS VARCHAR(100) = 'My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa';

SELECT  REPLACE(
            LEFT(@MyVariable, LEN(@MyVariable) - CHARINDEX('.', REVERSE(@MyVariable)))
        , '.', '') + 
        RIGHT(@MyVariable, CHARINDEX('.', REVERSE(@MyVariable)))

You replace all the dots in the part of the string before the last dot, and concatenate to that the part of the string from the last dot to the end.

Brakedown:

  • CHARINDEX('.', REVERSE(@MyVariable)) gives you the index of the last dot.
  • LEFT(@MyVariable, LEN(@MyVariable) - CHARINDEX('.', REVERSE(@MyVariable))) gives you the part of the string up to the last dot (inclusive).
  • REPLACE(LEFT(@MyVariable, LEN(@MyVariable) - CHARINDEX('.', REVERSE(@MyVariable))), '.', '') gives you that part of the string without the dots.
  • RIGHT(@MyVariable, CHARINDEX('.', REVERSE(@MyVariable))) gives you the last part of the string, including the last dot.

If your string don't contain any dots, it will simply return the same string.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40319

Here's an option that just uses string manipulation.

DECLARE
  @MyVariable VARCHAR(100) = 'My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa'
 ,@FixFor     CHAR(1)      = '.'         
 ,@LastHit    INT

First, find the position of the last occurrence of the target character:

SET @LastHit = len(@MyVariable) - charindex(@FixFor, reverse(@MyVariable)) + 1

Using that value, we can do some awkward string manipulation:

PRINT replace(left(@MyVariable, @LastHit - 1), @FixFor, '') + substring(@MyVariable, @LastHit, 100)

This could be turned into a single statement, but if you do this anyone who has to work with your code later on will hate you:

PRINT replace(left(@MyVariable, len(@MyVariable) - charindex(@FixFor, reverse(@MyVariable))), @FixFor, '') + substring(@MyVariable, len(@MyVariable) - charindex(@FixFor, reverse(@MyVariable)) + 1, 100)

You will have noted that T-SQL is not a good language for string manipulation. Whenever possible, leave formatting to the front-end applications, which do it very well.

Upvotes: 0

Thom A
Thom A

Reputation: 95557

If the . character, will always be at the end, this is as simple as:

SELECT REPLACE(@MyVariable,'.','') + '.';

Just remove all the . characters, and then add the one at the end back.

Edit: With the correct comment, you would need to do something like this:

DECLARE @MyVariable AS VARCHAR(50) = 'My Name. Is Ibrahim. AND. I am. Searching For. A. Solution. aa';

SELECT REPLACE(LEFT(V.MyString, LEN(MyString) - (CI.I - 1)),'.','') + RIGHT(V.MyString,CI.I)
FROM (VALUES(@MyVariable),('This.is.a.string'))V(MyString)
     CROSS APPLY (VALUES(NULLIF(CHARINDEX('.',REVERSE(V.MyString)),1)))CI(I);

Note that for your example string, the return value is 'My Name Is Ibrahim AND I am Searching For A. '; as the initial string is over 50 characters and so truncated.

Upvotes: 0

Related Questions