• Welcome, Guest. Please login.
 
December 13, 2019, 08:43:25 pm

News:

Welcome to the SQLitening support forums!


Search text in column header

Started by varungarg28, March 10, 2018, 11:25:50 pm

Previous topic - Next topic

varungarg28

March 10, 2018, 11:25:50 pm Last Edit: March 11, 2018, 03:44:31 am by varungarg28
Hi All,

I am using SQLite and have to create a query where I have to first search and select a column on the basis of some text in its header and then run mathematical operations on the data in that column. Like one column header has some text and a part of which is the day Friday. So I should be able to use something like, like '%Friday%' and then suppose count all the numbers under that column. Is there any way to do so?

Thanks in Advance

cj

March 11, 2018, 07:22:08 am #1 Last Edit: March 11, 2018, 07:31:46 am by cj
You can create another table and USE LIKE or INSTR and return the column name.
Sounds like you are not using SQLitening, but here is how I would do it.

Since SQLite is an embedded database another table can be eliminated
by using INSTR on headers of an array where elements relate to column name elements.

If you are looking for a pure SQLite method it will take some more creative work.
SQLite forum (if you haven't been there)  http://sqlite.1065341.n5.nabble.com/

GLOBAL gsColName(),gsHeader() AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL sColName AS STRING
  InitGlobals
  CreateSampleData
  sColName = FindColumnName("Paul")
  IF LEN(sColName) THEN ? MySQL("select " + sColName + " from T1")
END FUNCTION
'-------------------------------------------------------------------
FUNCTION MySql(sql AS STRING) AS STRING
  LOCAL sArray() AS STRING
  slSelAry sql,sArray(),"Q9"
  IF UBOUND(sArray)> 0 THEN FUNCTION = JOIN$(sArray(),$CR)
END FUNCTION
'-------------------------------------------------------------------
FUNCTION FindColumnName(s AS STRING) AS STRING
  LOCAL x AS LONG
  FOR x = 1 TO 3
    IF INSTR(gsHeader(x),s) THEN
      FUNCTION=gsColName(x)
      EXIT FUNCTION
    END IF
  NEXT
END FUNCTION
'-------------------------------------------------------------------
SUB InitGlobals
  DIM gsColName(1 TO 3)
  DIM gsHeader(1 TO  3)

  gsColName(1) = "people"
  gsColName(2) = "places"
  gsColName(3) = "animals"

  gsHeader(1) = "Bern Fim Paul"
  gsHeader(2) = "London New York Chicago
  gsHeader(3) = "dog pig horse cow"

END SUB
'-------------------------------------------------------------------
SUB CreateSampleData
  LOCAL x AS LONG
  LOCAL s AS STRING
  slOpen "junk.db3","C"
  slexe "drop table if exists T1"
  slexe "create table if not exists T1(people,places,animals)
  FOR x = 1 TO 11
    s= USING$("insert into T1 values('heidi#'_,'boston#'_,'pig#')",x,x,x)
    slexe s
  NEXT
END SUB
                                       
               

Bern Ertl

I don't think there is a way to enumerate column names in SQL syntax, so I don't think you can search and select columns with pure SQL.  You are going to have to use SQLite and/or SQLitening functions to enumerate/search the column names.

cj

March 12, 2018, 01:06:11 pm #3 Last Edit: March 12, 2018, 01:11:36 pm by cj
> select a column on the basis of some text in its header
I'd like to know what a header is.  It is text within the column name?
I  was thinking it wa extra text added to the column as a heading after the recordset was created.
If it is actually the column name we definitely have a way to search for the column names in each created recordset.