LTRIM ending values throughout entire column using SQL

Question

I have a table with a column named 'ProjectCode' with all values in the column set up as such: 123.21.000. I am looking to write something to iterate through the whole column and remove the ending .000 from all of the entries. Here is an example of the many things I've tried.

select ltrim("Project Code", '.000') 
from "Project Code"

Answer 1

There are a few ways of doing such something, but since we don't known enough of your data we can only guess

Here are a few posibilities

declare @t table (ProjectCode varchar(50))
insert into @t values ('123.21.000'), ('000123.21.000')

select ProjectCode,
       -- just remove the last 3 chars, whatever they are
       left(ProjectCode, len(ProjectCode) - 3) as [3 last removed],
        
       -- another way of removing the last 3 chars
       substring(ProjectCode, 1, len(ProjectCode) - 3) as [3 last removed substr],
       
       -- remove all '0' chars, both left and right from the value
       trim('0' from ProjectCode) as [trim all zeros],            
       
       -- replace any occurence of '000' by ''
       replace(ProjectCode, '000', '') as [replace 000]             
from @t

This returns

ProjectCode	3 last removed	3 last removed substr	trim all zeros	replace 000
123.21.000	123.21.	123.21.	123.21.	123.21.
000123.21.000	000123.21.	000123.21.	123.21.	123.21.

Answer 2

If you're on a fully supported version of SQL Server you could use TRIM:

SELECT TRIM('0.' FROM ProjectCode) AS ProjectCode
FROM dbo.YourTable;

This assumes that your column ProjectCode doesn't have leading 0s or .'s (i.e. '00.123.000' or '0123.456.1').

Answer 3

You can use PARSENAME function to get the first two values in the string:

DECLARE @Str VARCHAR(100) = '123.21.000'; 
SELECT CONCAT(PARSENAME(@Str , 3) , '.', PARSENAME(@Str , 2))

Result:   123.21

Your query would look something like this:

SELECT CONCAT(PARSENAME("Project Code" , 3) , '.', PARSENAME("Project Code" , 2))
FROM "Project Code"

But if you have variable number of '.' and you only want to get rid of the very last . and any value after that, then you can use the following query:

DECLARE @t TABLE(Col VARCHAR(100) )
INSERT INTO @t (Col)
VALUES ('123.21.000') , ('123.21.01.000'), ('123.00.011.0111.0000'), ('123'); 


SELECT REVERSE(SUBSTRING(REVERSE(Col) , CHARINDEX('.', REVERSE(Col)) + 1 , LEN(Col)))
FROM @t

Result: 
123.21
123.21.01
123.00.011.0111
123