LTRIM ending values throughout entire column using SQL-CodePudding

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"

CodePudding user response：

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').

CodePudding user response：

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.

CodePudding user response：

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