0

How exactly do I replace something by using wildcard characters?

I had tried with this but it doesn't work

UPDATE [dbo].[test]
SET [Fælge] = REPLACE([Fælge],'%ET%%%','')

I want all the ET** to be blank

My data looks like this and it goes from 4-12x10-24 ET0-99 plus a half like(4.5x13 ET35.5)

6x15 ET0|6.5x16 ET55|6x16 ET50|7x17 ET60|7x17 ET65

my data grouped and counted my data grouped

i want both ET and the numbers to be blank so the data just looks like

6x15 |6.5x16 |6x16 |7x17 |7x17
omini data
  • 407
  • 7
  • 29
  • You only want to blank out AFTER the characters 'ET' ?? Can you show expected outputs too? – Dave C Jun 16 '16 at 16:06
  • Also check here https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function – Dave C Jun 16 '16 at 16:10
  • i want t ET and the numbers to be blank so the data just looks like `6x15 |6.5x16 |6x16 |7x17 |7x17 ` – omini data Jun 16 '16 at 17:13
  • This is just awful. You have violated 1NF. Now you will have to first split this mess into rows. http://sqlperformance.com/2012/07/t-sql-queries/split-strings The next step will be to update those values like in the code in my answer. Last but not least you have to cram them all back into a single tuple again. http://www.sqlservercentral.com/articles/comma+separated+list/71700/ – Sean Lange Jun 17 '16 at 13:00

1 Answers1

0

I will assume that the sample data provided is indicating multiple rows. You can do this quite easily using LEFT and CHARINDEX.

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    SomeValue varchar(50)
)

insert #Something(SomeValue) values
('6x15 ET0'), 
('6.5x16 ET55'), 
('6x16 ET50'), 
('7x17 ET60'), 
('7x17 ET65')

update #Something
set SomeValue = rtrim(LEFT(SomeValue, CHARINDEX('ET', SomeValue) - 1))

select *
from #Something
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • so that would remove all the ET and the numbers? and i would have to type ('6x15 ET0'), ('6.5x16 ET55'), ('6x16 ET50'), ('7x17 ET60'), ('7x17 ET65') i have 27k records that have a diffren't combination of 4-12x10-24 ET0-99 plus some with decimal so that would be a long list – omini data Jun 16 '16 at 17:18
  • No you don't have to type in all the values. I did that because you didn't provide us sample data in a consumable format. Those values are just being inserted into the temp table. All you would need is the update statement. However, now that you have posted "sample data" it seems my worst fears have been proven correct. You have denormalized data with multiple values in a single tuple. This violates 1NF and is going to cause you untold amounts of pain for the duration of the life of this system. – Sean Lange Jun 17 '16 at 12:57