Sql notes

From raju

convert date to YYYYMMDD form

select fld1, convert(varchar(10), fld_date, 112) fld_date, fld3, fld4
from table 
where fld1 in ('valA' ,'valB')

convert date to YYYY-MM-DD

To convert a date to iso string in sybase ase

select str_replace( convert( varchar, ColumnName, 111), '/', '-') NewColumnName

tag | convert date to iso string

split a string

One column in a sql table is of the form


Split this into two columns - every thing but the last field, last field.


select substring(col_name, 1, len(col_name)-charindex('|', reverse(col_name))) all_but_last_field,
       substring(col_name, len(col_name)-charindex('|', reverse(col_name))+2, len(col_name)) last_field,
  from table_name


  • The length of the string, N, is given by len(col_name).
  • The range of charindex is [1:N].
  • If P is the position of the last delimiter when counted from end, its position would be N-P+1 when counted from the beginning.
  • So, the fields that we want are [1:N-P],[N-P+2:N]
  • P is given by charindex('|', reverse(col_name))

tags | split string sybase sql table, split a string and get the last field

Ref:- http://stackoverflow.com/questions/14412898/split-string-and-take-last-element

get most recent record

Say we have a table with three fields - cusip, as_of and quantity. New entries are added if and only if the quantity of a cusip changes from previous day. The task is to find the most recent quantity for a given date and a list of cusips.

Assume the list of cusips is given as "000324AA1","000361AH8", "00103YAE1","00130HBH7"

Assume the date of interest is '20160701'

The query would be something like

select a.cusip, convert(varchar(8), a.as_of, 112) as 'date', a.quantity
from tbl a
inner join (
    select cusip, max(as_of) as as_of
    from tbl
    where cusip in ("000324AA1","000361AH8", "00103YAE1","00130HBH7")
    and convert(varchar(8), as_of, 112) <= '20160701'
    group by cusip
    ) b
    on a.cusip=b.cusip and a.as_of = b.as_of

tags | query the latest record

Ref:- http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record

enclose each line in quotes

Use case 1:

head -n1 foo.csv | tr ',' '\n' | sed 's/\(.*\)/"\1"/g'

For example

% echo "foo1,foo2,bar1,bar2" | tr ',' '\n' | sed 's/\(.*\)/\tstructField("\1", ""),/g'
        structField("foo1", ""),
        structField("foo2", ""),
        structField("bar1", ""),
        structField("bar2", ""),

Use case 2:

% cat ~/bin/sqlin
sed 's/\(.*\)/"\1"/g' | tr '\n' ',' | sed -e 's/^/(/g' -e 's/,$/)\n/g'

% chmod +x ~/bin/sqlin

Sample input:

% cat foo.txt

Sample usage:

% cat ~/x/foo.txt | tail -n +2 | ~/bin/sqlin

tags | quote string for sql in clause

remove extra spaces at the end


stackoverflow links

External Links