remove only number in the given string

• srinudw88

  SSC Veteran

  Points: 298

  More actions

  June 11, 2018 at 12:53 pm

  #351727

  HI Friends ,I have one doubt in sql server .
  how to remove only numeric only in the given string.

  sample data
  declare @t table (a varchar(50))
  insert into @t values ('Nxyz_tV_201806040918')
  insert into @t values ('av_OrV_rW_20180604000000')
  insert into @t values ('xt_tNT_tW_20180531_xyz')
  insert into @t values ('c:\abc_Pun_yb_20180604000000')
  insert into @t values ('c:\Uj_To_EDW_20180531_sr')
  insert into @t values ('c:\USFC_TRN_EDW_20180604000000_tx')

  expected output like below:
  Nxyz_tV
  av_OrV_rW
  xt_tNT_tW_xyz
  c:\abc_Pun_yb
  c:\Uj_To_EDW_sr
  c:\USFC_TRN_EDW_tx

  I tried like below:
  SELECT LEFT(a, LEN(a) - CHARINDEX('_',REVERSE(a)))
  FROM @t
  another way I tried
  SELECT
  REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE
  (REPLACE (a, '0', ''),
  '1', ''),
  '2', ''),
  '3', ''),
  '4', ''),
  '5', ''),
  '6', ''),
  '7', ''),
  '8', ''),
  '9', '')
  FROM @t

  above query is not given expected result.

  please tell me how to write query to achive this task in sql server .

• Thom A

  SSC Guru

  Points: 99133

  More actions

  June 11, 2018 at 1:28 pm

  #1993606

  What was wrong with the answers you received on Stack Overflow?

  Thom~

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

• DesNorton

  SSC-Insane

  Points: 24401

  More actions

  June 11, 2018 at 1:34 pm

  #1993607

  Combine your 2 statements like this ....
  SELECT a = LEFT(a, LEN(a) - CHARINDEX('_',REVERSE(a)))
FROM (
  SELECT
   REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE
   (REPLACE (a, '0', ''),
   '1', ''),
   '2', ''),
   '3', ''),
   '4', ''),
   '5', ''),
   '6', ''),
   '7', ''),
   '8', ''),
   '9', ''),
   '__', '_') as a
  FROM @t
) AS src

  ---

  How to post data/code on a forum to get the best help.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 11, 2018 at 1:58 pm

  #1993609

  Not really a suggestion because the solution posted by DesNorton should be better but I just wanted to have some fun.
  
SELECT * ,
  STUFF(( SELECT '_' + dsk.Item
    FROM dbo.DelimitedSplit8K(t.a, '_') AS dsk
    WHERE dsk.Item LIKE '%[^0-9]%'
    FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM @t AS t


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• shahm10

  SSCrazy

  Points: 2217

  More actions

  June 11, 2018 at 6:10 pm

  #1993620

  
SELECT SUBSTRING(a,0,PATINDEX('%[0-9]%',a)-1) + REVERSE(SUBSTRING(REVERSE(a),0,PATINDEX('%[0-9]%',REVERSE(a)))) AS A
FROM #t


• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  June 11, 2018 at 7:31 pm

  #1993621

  Is there any guarantee that ALL of the numeric characters will ALWAYS be contiguous in EVERY string?

  --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)