Remove dots from column values

iridescence 0
Viewed 54

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
tsql replace sql-server sql
2 Answers

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

enter image description here

John Cappelletti0 
 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
userhAAv0
Related