Category Archives: SQL

Code Snippets SQL

MySQL DATE_FORMAT

1 de Fevereiro de 2017 – 11:48

DATE_FORMAT(date,format)

%a    Abbreviated weekday name (Sun-Sat)
%b    Abbreviated month name (Jan-Dec)
%c    Month, numeric (0-12)
%D    Day of month with English suffix (0th, 1st, 2nd, 3rd, ?)
%d    Day of month, numeric (00-31)
%e    Day of month, numeric (0-31)
%f    Microseconds (000000-999999)
%H    Hour (00-23)
%h    Hour (01-12)
%I    Hour (01-12)
%i    Minutes, numeric (00-59)
%j    Day of year (001-366)
%k    Hour (0-23)
%l    Hour (1-12)
%M    Month name (January-December)
%m    Month, numeric (00-12)
%p    AM or PM
%r    Time, 12-hour (hh:mm:ss followed by AM or PM)
%S    Seconds (00-59)
%s    Seconds (00-59)
%T    Time, 24-hour (hh:mm:ss)
%U    Week (00-53) where Sunday is the first day of week
%u    Week (00-53) where Monday is the first day of week
%V    Week (01-53) where Sunday is the first day of week, used with %X
%v    Week (01-53) where Monday is the first day of week, used with %x
%W    Weekday name (Sunday-Saturday)
%w    Day of the week (0=Sunday, 6=Saturday)
%X    Year for the week where Sunday is the first day of week, four digits, used with %V
%x    Year for the week where Monday is the first day of week, four digits, used with %v
%Y    Year, numeric, four digits
%y    Year, numeric, two digits

The following script uses the DATE_FORMAT() function to display different formats. We will use the NOW() function to get the current date/time:

DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
DATE_FORMAT(NOW(),’%m-%d-%Y’)
DATE_FORMAT(NOW(),’%d %b %y’)
DATE_FORMAT(NOW(),’%d %b %Y %T:%f’)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-2014
04 Nov 14
04 Nov 2014 11:45:34:243

Permalink | Comment (0)
Code Snippets MySQL SQL

Mysql Proper Case

13 de Outubro de 2015 – 9:49
    DROP FUNCTION IF EXISTS proper;
    SET GLOBAL  log_bin_trust_function_creators=TRUE;
    DELIMITER |
    CREATE FUNCTION proper( str VARCHAR(128) )
    RETURNS VARCHAR(128)
    BEGIN
      DECLARE c CHAR(1);
      DECLARE s VARCHAR(128);
      DECLARE i INT DEFAULT 1;
      DECLARE bool INT DEFAULT 1;
      DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
      SET s = LCASE( str );
      WHILE i < LENGTH( str ) DO 
        BEGIN
          SET c = SUBSTRING( s, i, 1 );
          IF LOCATE( c, punct ) > 0 THEN
            SET bool = 1;
          ELSEIF bool=1 THEN 
            BEGIN
              IF c >= 'a' AND c <= 'z' THEN 
                BEGIN
                  SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
                  SET bool = 0;
                END;
              ELSEIF c >= '0' AND c <= '9' THEN
                SET bool = 0;
              END IF;
            END;
          END IF;
          SET i = i+1;
        END;
      END WHILE;
      RETURN s;
    END;
    |
    DELIMITER ;
Permalink | Comment (0)
Code Snippets SQL

Detect invalid(sintax) emails

11 de Abril de 2014 – 11:21

Detect invalid(sintax) emails in db

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'
Permalink | Comment (0)
Code Snippets SQL

Query to list number of records in each table in a database

11 de Abril de 2014 – 10:56

sql server 2005



SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
Permalink | Comment (0)
Backup MySQL Security SQL Tips & tricks Tutorials web services Windows server

Schedule Mysql Backups to Amazon S3 in Windows server 2008 R2

12 de Junho de 2013 – 20:31

1 – Access Amazon Services, S3
2 – Create a New Bucket if there’s no one.
3 – Create credentials in  IAM Amazon Services
4 – Download the tool s3.exe for windows, from s3.codeplex.com

read more »

Permalink | Comment (0)
Microsoft SQL SQL Server

SQL Server, Common Table Expressions

14 de Março de 2013 – 12:17

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

read more »

Tags , | Permalink | Comment (0)
SQL SQL Server Transact-SQL

Recursive Queries using Common Table Expressions (CTE) in SQL Server

8 de Março de 2013 – 10:58

Given the example , hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused.

Syntax

WITH cte_alias (column_aliases)
AS
(
cte_query_definition   --initialization
UNION ALL
cte_query_definition2 --recursive execution
)
SELECT * FROM cte_alias

Sample (from Root nodes to Leaf  noes)

WITH Managers AS
(
--initialization
SELECT EmployeeID, LastName, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use.

WITH xEmployees AS
(
--initialization
SELECT  EmployeeID, LastName, ReportsTo
FROM Employees
WHERE EmployeeID IN (55,98,65,12)  -- sample Leaf nodes to filter/use
UNION ALL
--recursive execution
SELECT a.EmployeeID,a.LastName,a.ReportsTo
FROM Employees a INNER JOIN xEmployees m
ON a.EmployeeID = m.reportsTo
)
SELECT Distinct EmployeeID, LastName, ReportsTo  FROM xEmployees

!Importante to use “Distinct” statement!

Permalink | Comment (0)
Code Snippets Databases MySQL SQL

mysql ‘Proper case’ formating a column?

21 de Novembro de 2012 – 15:27

How to do something like:

Proper(“GALHANO.COM”) = “Galhano.com”

there’s no builtin function to do this but we combine CONCAT and SUBSTRING:

CONCAT(UCASE(SUBSTRING(`fieldName`, 1, 1)),LOWER(SUBSTRING(`fieldName`, 2)))
Permalink | Comment (0)
SQL

Finding Duplicates with SQL

11 de Maio de 2012 – 16:05

Here’s query for finding duplicates in a table. Suppose you want to find all Usernames values in a table that exist more than once:


SELECT Username, COUNT(Username) AS NumOccurrences
FROM   Users
GROUP BY Username
HAVING      (COUNT(Username) &gt; 1)
Tags , | Permalink | Comment (0)
SQL

Count words in table

8 de Maio de 2012 – 20:13
SELECT SUM( LENGTH(myfield) - LENGTH(REPLACE(myfield, ' ', ''))+1) as nreg
FROM myTable
Permalink | Comment (0)

Página seguinte »

Página seguinte »