Remove dots from column values-CodePudding

I would like to remove a series of dots from the values in a table using SQL

Example:

ID        Values
1         Automobiles……………………………………………………………………………………………………………………………………………….
2         Furniture……………………………………………………………………………………………………………………………………………………………
3         Leasehold Improvements…………………………………………….
4         Land………………………………………………………..……………………………………………………………………………………..
5         Equipment………………………………………….………………………………………….

Required output:

ID        Values
1         Automobiles
2         Furniture
3         Leasehold Improvements
4         Land
5         Equipment

CodePudding user response：

Assuming no leading or embedded ellipses. Note the "Fail-Safe" [Values] '…'

Declare @YourTable Table ([ID] varchar(50),[Values] varchar(500))  Insert Into @YourTable Values 
 (1,'Automobiles……………………………………………………………………………………………………………………………………………….')
,(2,'Furniture……………………………………………………………………………………………………………………………………………………………')
,(3,'Leasehold Improvements…………………………………………….')
,(4,'Land………………………………………………………..……………………………………………………………………………………..')
,(5,'Equipment………………………………………….………………………………………….')
 
Select *
      ,NewValue = substring([Values],1,charindex('…',[Values] '…')-1)
 from @YourTable

Results

CodePudding user response：

 Declare @YourTable Table ([ID] varchar(50),[Values] varchar(500))  Insert Into @YourTable Values 
 (1,'Automobiles……………………………………………………………………………………………………………………………………………….')
,(2,'Furniture……………………………………………………………………………………………………………………………………………………………')
,(3,'Leasehold Improvements…………………………………………….')
,(4,'Land………………………………………………………..……………………………………………………………………………………..')
,(5,'Equipment………………………………………….………………………………………….')
 
Select *
     ,replace(replace([values],'…',''),'.','')
 from @YourTable