Die whitespace, DIE !!

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  June 4, 2018 at 4:06 pm

  #348972

  Hi all, 

  Trying to delete some white spacebar as follows...

      

  /****** Object:  Table [dbo].[ranks]    Script Date: 04/06/2018 22:43:46 ******/
  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  Create TABLE [dbo].[ranks]( [name] [nvarchar](50) NULL
  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[ranks]
             (
             [name]
         )
       VALUES

    ('White/ Yellow Stripe'),
  ('White/ Orange Stripe'),
  ('White / Blue Stripe'),
  ('White / Green Stripe'),
  ('White/ Red Stripe'),
  ('White / Black Stripe')
  GO

  SELECT  *
  From ranks
  Where name like '% /%' Or name like '%/ %'
  

  However, the second Select..Replace undoes the work of the first, hopefully I'm simply not using the statement correctly and this is a simple fix..
   

  SELECT  replace(name, ' /', '/') from ranks
  SELECT  replace(name, '/ ', '/') from ranks
  

  TIA

• frederico_fonseca

  SSCoach

  Points: 16014

  More actions

  June 4, 2018 at 4:13 pm

  #1992912

  is this what you expect?
  
if object_id('dbo.ranks') is not null
  drop table dbo.ranks;

  create table dbo.ranks
  ( name nvarchar(50) null
  )
  on [PRIMARY]
go

  insert into dbo.ranks
   ([name]
   )
values ('White/ Yellow Stripe')
     , ('White/ Orange Stripe')
     , ('White / Blue Stripe')
     , ('White / Green Stripe')
     , ('White/ Red Stripe')
     , ('White / Black Stripe')
go

  select *
  , replace(replace(name, ' /', '/'), '/ ', '/')
from ranks
where name like '% /%'
  or name like '%/ %'

  if object_id('dbo.ranks') is not null
  drop table dbo.ranks;


• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  June 4, 2018 at 4:30 pm

  #1992913

  As a result set, yes, but I want to actually commit the change.

• JaybeeSQL

  Hall of Fame

  Points: 3501

  More actions

  June 4, 2018 at 5:10 pm

  #1992916

  I got it...

  Begin tran
Update ranks
Set name =
replace(replace(name, ' /', '/'), '/ ', '/')
from ranks
Where name like '% /%' Or name like '%/ %'

  Thanks bud 🙂

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  June 4, 2018 at 5:13 pm

  #1992917

  if object_id('dbo.ranks') is not null drop table dbo.ranks;

  go

  create table dbo.ranks(name nvarchar(50) null);

  go

  insert into dbo.ranks([name])

  values ('White/ Yellow Stripe')

       , ('White/ Orange Stripe')

       , ('White / Blue Stripe')

       , ('White / Green Stripe')

       , ('White/ Red Stripe')

       , ('White / Black Stripe');

  select r.name from dbo.ranks r;

  update r

  set name = replace(replace(name, ' /', '/'), '/ ', '/')

  from ranks r

  where name like '% /%'

    or name like '%/ %';

  select r.name from dbo.ranks r;

  if object_id('dbo.ranks') is not null

    drop table dbo.ranks;

  go

  
  

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  June 7, 2018 at 6:39 am

  #1993198

  Hallo All,

  Some years ago I wrote a 'replace2' function which can do multiple replaces in one go.
  It does the same job as a multiple nested replace, but the interface is easier to use.

  See :
  Multiple replace.

  Examples :
  
SET @UU = dbo.Replace2(@uu, 'aaa|bb|c|d|e|f|g|h','11|2|3|4444|5|6|7|8') 

  -- To remove redundant whitespace for example, the following can be used:
SELECT dbo.Replace2(afield, '                |        |    |  |  ',' | | | | ')  FROM ....

  -- To change any letter in a 'A' en any digital character in a '9'
SELECT dbo.Replace2(afield, 'a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z|0|1|2|3|4|5|6|7|8|9',
                                                'A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|A|9|9|9|9|9|9|9|9|9|9')     FROM ....


  This function has been very usefull over the years. It does not do anything 'new', nor is it faster dan a nested replace. But it is so much easier on the code writing. I even build a simple calculater using nothing but the replace2 function. Other uses are for checking patterns, removing all sort of 'constructs' from within a string. Even used it for decomposing and analysing Views.

  If this is helpfull to anybody, please give a reply.

  Ben Brugman.