Remove 6 digits and 8 digits from texts

• azdeji

  SSCrazy

  Points: 2535

  More actions

  July 13, 2022 at 11:13 am

  Go to Answer

  #4062690

  Hi

  I want to remove 6 digits and 8 digits from a text column see example in picture

  There are 6 digits and 8 digits in the 'provider' column that I want to remove. See picture for example

  At the moment, I'm using a convoluted CTE method to accomplish this.

  Thanks

  

  SQL DDL --

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING ON

  GO

  CREATE TABLE [dbo].[Remove_account_sort](

  [Provider] [varchar](500) NULL

  ) ON [PRIMARY]

  GO

  SET ANSI_PADDING OFF

  GO

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"Deckow and Sons - ifnvkfnljndfbl- 550003 - 07341477 - "')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"Walker-Wehner - ufnidmk jfljslj-s nsf -dfdv - 085738 - 37583738 - "')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"Deckow-Greenfelder - iuYHBYBYI - - 501110221 -"')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"Legros, Thompson and Pollich 97hrougvwrlnivpnrwibn858- suhvnurnin - siorvogirnlnrli - 174846 - 09746574 - "')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"Cremin, Olson and Thiel - 246786 - 34674747 - "')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"dufbiuivunruvvnusn - 738 - 93959nsiniiw - 164858 - 04648751 - "')

  INSERT [dbo].[Remove_account_sort] ([Provider]) VALUES (N'"4738628872 wuhsuohuhvwu - susiihshk -73838 - "')

   

• Thom A

  SSC Guru

  Points: 99133

  More actions

  July 13, 2022 at 11:39 am

  Answer

  #4062703

  Seems you could use PATINDEX here, to find the pattern, and the STUFF the remove the characters. Assuming you want the 2 hyphens (and whitespace) left in situ, it would look like this:

  SELECT ISNULL(STUFF(Provider, NULLIF(PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9] - [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', Provider),0), 17,''),Provider)
  FROM dbo.Remove_account_sort;

  If you don't want to leave the (leading?) hyphen and surrounding whitespace, then you simply need to adjust the pattern for PATINDEX and the length of characters to be replaced in the STUFF .

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• azdeji

  SSCrazy

  Points: 2535

  More actions

  July 13, 2022 at 11:57 am

  #4062705

  Thanks Thom A!

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  July 13, 2022 at 3:43 pm

  #4062830

  You need to fix the example you posted.  You tried to obfuscate some information with a Magenta Marker but it was actually a hiliter and we can read the data that we are supposed to be able to read.

  Ah... never mind.  I see it was supposed to be a hiliter...  more coffee please.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• July 22, 2022 at 9:14 am

  This was removed by the editor as SPAM