Sql-server – Remove Dot at end of line of the string in SQLserver

sql servert-sql

I would to like to remove the dot at end of the line alone if exists in the string in SQL server.

Example:

ABC Private's co., ltd.

Required output:

ABC Private's co., ltd

Best Answer

You can use like to check for a . at the end of the string.

declare @S nvarchar(max) = 'ABC Private''s co., ltd.';

select case when @S like '%.' 
         then left(@S, len(@S) - 1) 
         else @S 
       end;

Related Solutions

Sql-server – Space at the end of string

When checking if a string is empty or not, append a non-space character to it and compare the result to the same character. This

@string + '.' <> '.'

will evaluate to TRUE only when the @string is truly empty. The @string's trailing spaces will count (because they will no longer be trailing after appending the .).

And you can go similarly about finding the length. This

LEN(@string + '.')

will only ever evaluate to 1 if the @string is '', not when it's ' ' or ' ' or anything of the kind.

By the way, when you want the SUBSTRING function to return a substring from a certain position to the end of the string, you can specify any fairly large number as the length parameter, no need to use LEN(). Very often something like

SUBSTRING(@string, 2, 999999999)

would do.

SQL Server – Searching for X at the Beginning of a Line in Multi-Line String

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply:

WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%'
------^^^^^^^^ in case the text *starts* with a lettered list

Note this will also find:

Some sentence is here

a. foo

Some more text later

If you need to only return rows where there is a list with two or more lines, you may not want to do this in the database, because it would require much more complicated parsing. In fact in that case - and if I couldn't perform the parsing outside the database (or in the database with CLR RegEx), I would probably use an ordered split function.

Best Answer

Related Solutions

Sql-server – Space at the end of string

SQL Server – Searching for X at the Beginning of a Line in Multi-Line String

Related Question