MySQL Archives - Bogiecom.com

MySQL Regex Column Name Selection

Posted on January 23, 2013 by bc-admin

If you’re like me, or if you’re not you might be at some point in your life… You may have a reason to lazily select all columns where the column name matches a pattern of sorts.

In my particular case, the data was organized in a fashion where several columns had the same name, but were also numbered. Since there were about 50 columns with the same pattern, I didn’t feel like writing out each column name.

SELECT CONCAT('SELECT ', 
    GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
    ' FROM ', 'test_db.test_table')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'test_db' 
    AND TABLE_NAME = 'test_table'
    AND COLUMN_NAME RLIKE '^FieldDesc';

SELECT CONCAT('SELECT ',

GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),

' FROM ', 'test_db.test_table')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'test_db'

AND TABLE_NAME = 'test_table'

AND COLUMN_NAME RLIKE '^FieldDesc';

And this outputs the select statement for you

SELECT DISTINCT FieldDesc6, FieldDesc7, FieldDesc8, 
                FieldDesc9, FieldDesc10, FieldDesc11, 
                FieldDesc12, FieldDesc13, FieldDesc14, 
                FieldDesc15, FieldDesc16, FieldDesc17, 
                FieldDesc18, FieldDesc19, FieldDesc20, 
                FieldDesc21, FieldDesc22, FieldDesc23,
                FieldDesc24, FieldDesc25, FieldDesc26, 
                FieldDesc27, FieldDesc28, FieldDesc29, 
                FieldDesc30, FieldDesc31, FieldDesc32, 
                FieldDesc33, FieldDesc34, FieldDesc35, 
                FieldDesc36, FieldDesc37, FieldDesc38, 
                FieldDesc39, FieldDesc40, FieldDesc41, 
                FieldDesc42, FieldDesc43, FieldDesc44, 
                FieldDesc45, FieldDesc46, FieldDesc47, 
                FieldDesc48, FieldDesc49, FieldDesc50, 
                FieldDesc51, FieldDesc52, FieldDesc53, 
                FieldDesc54, FieldDesc55, FieldDesc56 
                  FROM test_db.test_table;

SELECT DISTINCT FieldDesc6, FieldDesc7, FieldDesc8,

FieldDesc9, FieldDesc10, FieldDesc11,

FieldDesc12, FieldDesc13, FieldDesc14,

FieldDesc15, FieldDesc16, FieldDesc17,

FieldDesc18, FieldDesc19, FieldDesc20,

FieldDesc21, FieldDesc22, FieldDesc23,

FieldDesc24, FieldDesc25, FieldDesc26,

FieldDesc27, FieldDesc28, FieldDesc29,

FieldDesc30, FieldDesc31, FieldDesc32,

FieldDesc33, FieldDesc34, FieldDesc35,

FieldDesc36, FieldDesc37, FieldDesc38,

FieldDesc39, FieldDesc40, FieldDesc41,

FieldDesc42, FieldDesc43, FieldDesc44,

FieldDesc45, FieldDesc46, FieldDesc47,

FieldDesc48, FieldDesc49, FieldDesc50,

FieldDesc51, FieldDesc52, FieldDesc53,

FieldDesc54, FieldDesc55, FieldDesc56

FROM test_db.test_table;

Changing SQL Schema in Mysql Dump Files using Powershell

Posted on June 14, 2012 by bc-admin

Quick way to change the schema name in all mysql dump files in a directory using Powershell. Or you could just use it to change a string in files of particular type in a directory

C:\Users\SloshDev> foreach($f in gci("*.sql")){ 
              (Get-Content $f.fullname) | %{ $_ -replace "magento_1_9","magento_1_12" } | Set-Content $f.fullname }

1 2	C:\Users\SloshDev> foreach($f in gci("*.sql")){ (Get-Content $f.fullname) \| %{ $_ -replace "magento_1_9","magento_1_12" } \| Set-Content $f.fullname }

Here’s how I changed the default character set from latin1 to utf8. May not want to use if different columns use different character sets.

C:\Users\SloshDev> foreach($f in gci("*.sql")){ 
      (Get-Content $f.fullname) | %{ $_ -replace "latin1","utf8" } | Set-Content $f.fullname 
     }

C:\Users\SloshDev> foreach($f in gci("*.sql")){

(Get-Content $f.fullname) | %{ $_ -replace "latin1","utf8" } | Set-Content $f.fullname

}

Magento Custom Form Field Validators using Prototype (MySQL Date Format)

Posted on November 9, 2011 by bc-admin

Here I needed to make sure that the user input was formatted correctly for a MySQL database. The javascript calendar code was set to automatically input the date into the text field in the correct format, but the user still had the option to type it in manually. This was the Prototype validator I used to verify that the user had entered it correctly as ‘YYYY-MM-DD’:

<script type="text/javascript">
  Validation.add('required-date-format','Please Enter date as YYYY-MM-DD. Click the calendar icon to select a date.', function(v) {
       return  !Validation.get('IsEmpty').test(v) && /^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9])$/.test(v);  });
       var warrantyregistrationForm = new VarienForm('warrantyregistrationForm', true);
</script>

Validation.add('required-date-format','Please Enter date as YYYY-MM-DD. Click the calendar icon to select a date.', function(v) {

return !Validation.get('IsEmpty').test(v) && /^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9])$/.test(v); });

var warrantyregistrationForm = new VarienForm('warrantyregistrationForm', true);

</script>