TSQL query help

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  February 3, 2017 at 2:31 am

  #318271

  Hi Experts,

  Need some tsql help.

  create table #temptbl
  (
  c1 varchar(100)
  )

  insert into #temptbl
  select ', , , , , , , 4.0, 4.0,'
  union all
  select ', , , ,4.0, 4.0, 4.0,'
  union all
  select ', , , ,4.0, 4.0, 4.0, , , ,'
  go

  select * from #temptbl

  My output should like this. Can anybody help.

  /*
  4.0,4.0
  4.0,4.0,4.0
  4.0,4.0,4.0
  */

• Thom A

  SSC Guru

  Points: 99143

  More actions

  February 3, 2017 at 2:52 am

  #1926535

  Why are you storing comma delimited strings in a table? It's really not a good idea.

  I've got it working using the DelimitedSplit8K function, but what is your real goal here?
  SELECT STUFF((SELECT ',' + DS.Item
              FROM dbo.DelimitedSplit8K (tt.c1, ',') DS
              WHERE DS.Item NOT IN ('', ' ')
              FOR XML PATH('')), 1, 1, '')
FROM #temptbl tt;

  Thom~

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

• Neil Burton

  SSC-Insane

  Points: 23102

  More actions

  February 3, 2017 at 2:57 am

  #1926538

  I've gone down a route that doesn't require anything that's not out-of-the-box.

  CREATE TABLE #temptbl
(
c1 VARCHAR(100)
)

  INSERT INTO #temptbl
SELECT ', , , , , , , 4.0, 4.0,'
UNION ALL
SELECT ', , , ,4.0, 4.0, 4.0,'
UNION ALL
SELECT ', , , ,4.0, 4.0, 4.0, , , ,'
UNION ALL
SELECT ', , , ,4.0, 4.0, 4.0, , , , , ,4.0'
UNION ALL
SELECT ', 4.0, , ,4.0, 4.0, 4.0, , ,4.0 , , ,4.0,'
GO

  SELECT REPLACE(REPLACE(REPLACE(c1,' ',''),',',''),'04','0,4')
FROM #temptbl

  DROP TABLE
#temptbl 

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  February 3, 2017 at 3:07 am

  #1926541

  Alternative solution using patindex
  😎

  IF OBJECT_ID(N'tempdb..#temptbl') IS NOT NULL DROP TABLE #temptbl;

  create table #temptbl
(
c1 varchar(100)
)

  insert into #temptbl
select ', , , , , , , 4.0, 4.0,'
union all
select ', , , ,4.0, 4.0, 4.0,'
union all
select ', , , ,4.0, 4.0, 4.0, , , ,'
go

  SELECT
  T.c1
 ,SUBSTRING(T.c1,PATINDEX('%[0-9]%',T.c1),(1 + (LEN(T.c1) - PATINDEX('%[0-9]%',T.c1)) - (PATINDEX('%[0-9]%',REVERSE(T.c1)) - 1)))  AS CleanStr
FROM #temptbl T;

  Output
  
c1                            CleanStr
----------------------------- --------------
, , , , , , , 4.0, 4.0,       4.0, 4.0
, , , ,4.0, 4.0, 4.0,         4.0, 4.0, 4.0
, , , ,4.0, 4.0, 4.0, , , ,   4.0, 4.0, 4.0

  

• Thom A

  SSC Guru

  Points: 99143

  More actions

  February 3, 2017 at 3:10 am

  #1926543

  My only concern with BWFC's is if the OP's data includes numbers other than 4 and 0.

  Thom~

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

• Neil Burton

  SSC-Insane

  Points: 23102

  More actions

  February 3, 2017 at 3:14 am

  #1926544

  Thom A - Friday, February 3, 2017 3:10 AM

  My only concern with BWFC's is if the OP's data includes numbers other than 4 and 0.

  A fair point but you work with what you've got 🙂

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537