CODEWITHSUNDEEP
sql-server
M Akela
M Akela

Reputation: 249

Distinct column names from comma separated string

Two reports are using columns of a table with following queries

SELECT [Employee Name], [Email_ID], [Region], [DOB], [Joining Date], [Gross Salary]   FROM [tblC6FD_Data_15_EmployeeData]   ORDER BY [Employee Name] ASC, [Email_ID] ASC, [Region] ASC, [DOB] ASC, [Joining Date] ASC, [Gross Salary] ASC

SELECT [Employee Name], [Region], [Email_ID], [DOB], [Leaving Date], [Basic Salary]   FROM [tblC6FD_Data_15_EmployeeData]   ORDER BY [Employee Name] ASC, [Region] ASC, [Email_ID] ASC, [DOB] ASC, [Joining Date] ASC, [Gross Salary] ASC

and i want distinct columns list of table that is being used by reports

Expected output

Employee 
Email 
Region 
DOB 
Joining Date 
Gross Salary 
Leaving Date 
Basic Salary

I have one idea to split it with , but a lot of confusions and complexity to avoid other strings after from keyword.

Can anyone suggest a simple solution.

Upvotes: 0

Views: 988

Answers (3)

M Akela
M Akela

Reputation: 249

i wrapped up it with following solution 

  declare @str nvarchar(max)
  set @str = (select ReportSQL from tblRB_Report where PK_ReportID = 15)
  set @str = REPLACE(@str,']',' ')
   set @str = REPLACE(@str,'[',' ')
  set @str = REPLACE(@str,'select',' ')

 set @str = SUBSTRING(@str,0,charindex('FROM',@str))
  --print @str


sELECT * from  [dbo].[StringSplit](@str,',')

Upvotes: 1

athabaska
athabaska

Reputation: 455

You have to use string functions for parsing, e.g. in C# it would be IndexOf() and Substring().

P.S. Seems this question is not related to sql-server btw.

Upvotes: 0

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

Are you talking about this?? this gives you list of column names

SELECT c.COLUMN_NAME
  FROM INFORMATION_SCHEMA.[COLUMNS] c  WHERE c.TABLE_NAME = 'table_name'

Upvotes: 0

Related Questions