GET DATE FROM MEMO/LONGTEXT ON MYSQL

Needed a way to get the date from a longtext (observation) column on MySQL without using regular expressions (Mysql 5.8), and I came up with this:

SET @DTDATA='WhiteSnake Tickets - Madson Square Garden. 15/12 7:30 PM. 209 11';

SELECT 
CASE 
WHEN (CHAR_LENGTH(@DTDATA) - CHAR_LENGTH(REPLACE (@DTDATA, '/', ''))) = 2 THEN STR_TO_DATE(SUBSTRING(@DTDATA, INSTR(@DTDATA,'/')-2,10), '%d/%m/%Y')
WHEN (CHAR_LENGTH(@DTDATA) - CHAR_LENGTH(REPLACE (@DTDATA, '/', ''))) = 1 THEN STR_TO_DATE(concat(SUBSTRING(@DTDATA, INSTR(@DTDATA,'/')-2,5),'/',YEAR(NOW())), '%d/%m/%Y')
ELSE
null
END ;


That will work with dd/mm/yyyy and dd/mm formats, and you can adapt to your needs changing the '%d/%m/%Y' format.


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

Converting a ASC character column to UTF-8

On a recent task, I had to import a simple text file for data enrichment purposes, and the "name" column when imported to SQL SERVER, got messed up, event after I changed the file encoding to UTF-8.

So basically, the data was like this:

"Jão Pedro Valênciano"

When it should be like this:

"João Pedro Valenciano"

After some googling, I found some dead end alleys and a good answer. On the SQL TEAM forum there is thread on the subject, proposing a function that solves the issue.

Bellow is the function and the link:

CREATE FUNCTION dbo.Utf8_to_utf16 (@s VARCHAR(8000)) 
returns NVARCHAR(4000) 
  BEGIN 
      IF @s IS NULL 
        RETURN NULL 

      DECLARE @n      INT, 
              @r      NVARCHAR(4000), 
              @cn     INT, 
              @octets INT, 
              @ch     NVARCHAR(2) 

      SET @r = N'' 

      WHILE 1 = 1 
        BEGIN 
            -- dubious: unexpected octets (0x80-0xBF, 0xF8-0xFF) are treated like 0x00-0x7F 
            SET @n = Patindex('%[ร€-รท]%', @s COLLATE latin1_general_bin 
                     ) 

            IF @n = 0 
              BEGIN 
                  SET @r = @r + @s 

                  BREAK 
              END 
            ELSE 
              BEGIN 
                  SET @r = @r + Substring(@s, 1, @n-1) 
                  SET @cn = Ascii(Substring(@s, @n, 1)) 

                  IF @cn <= 0xDF 
                    BEGIN 
                        SET @octets = 2 
                        SET @ch = Nchar(( @cn & 0x1F ) * 0x40 + ( Ascii( 
                                        Substring(@s, @n + 1, 1)) 
                                                                  & 
                                                                  0x3F )) 
                    END 
                  ELSE IF @cn <= 0xEF 
                    BEGIN 
                        SET @octets = 3 
                        SET @ch = Nchar(( @cn & 0x0F ) * 0x1000 + ( Ascii( 
                                        Substring(@s, @n + 1, 1) 
                                                                    ) 
                                                                    & 0x3F ) * 
                                                                            0x40 
                                        + 
                                        ( 
                                                  Ascii(Substring(@s, @n + 2, 1) 
                                                  ) 
                                                  & 
                                                  0x3F 
                                        )) 
                    END 
                  ELSE 
                    BEGIN 
                        -- code point in a supplementary plane: output UTF-16 surrogate pair 
                        SET @octets = 4 
                        SET @ch = Nchar((@cn & 0x07) * 0x100 + (Ascii(Substring( 
                                  @s 
                                  , @n 
                                  +1 
                                  , 1) 
                                  ) 
                                  & 
                                  0x3F 
                                  ) * 0x04 + ( 
                                  Ascii(Substring(@s, @n+2, 1)) & 0x30) / 0x10 + 
                                  0xD7C0) 
                                  + Nchar((Ascii(Substring(@s, @n+2, 1)) & 0x0F) 
                                  * 
                                  0x40 
                                  + ( 
                                  Ascii( 
                                  Substring(@s, @n+3, 1)) & 0x3F) + 0xDC00) 
                    END 

                  SET @r = @r + @ch 
                  SET @s = Substring(@s, @n + @octets, 8000) 
              END 
        END 

      RETURN @r 
  END 

go 


Link to it: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62406

Use it like this:
SELECT dbo.utf8_to_utf16 ('João Pedro Valênciano')

The result is:
João Pedro Valênciano


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

CREATE TABLE Script when you don't have the rights for it?

I was trying to get the CREATE TABLE script from a table in SQL SERVER, and due to permission rights, I was getting "Property text is not available for DefaultConstraint. This property may not exist for this object, or may not be retrievable due to insufficient access rights".

So how do I get the CREATE TABLE script when I dont have the rights for it?

I ended up finding the following script for it on crackoverflow: https://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table 

Here it is:

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

That gives me CREATE TABLE script for the table, that I need for a ETL job.

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06