Skip to main content

Posts

Wildcard Characters in T-SQL

The underscore character, when used with the LIKE operator, matches any single character. When using wildcards as literals, you need to square bracket the character, like '%[_]%[_]%[_]%' See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017#using-wildcard-characters-as-literals To help me limit the noise with the huge dataset, I employed a simple pattern match to bucket out returned rows.  I was more interested in the classifications and the broad relationships between the rows. I used something like CASE WHEN [Offering] LIKE '%_%_%_%' THEN 'XXX999_209999_X_X' My expectation was a field like 'AAA999_201600_A_B' would appear as 'XXX999_209999_X_X', and it did. But, it also changed 3 digit character entries like 'All' to 'XXX999_209999_X_X'. Turns out that _ is a single character wildcard, so '%_%_%_%' matched the word ‘Al...
Post a Comment
Read more

Change the logical Name of a MS SQL database

The process to change the logical name for the database and the log file is  the same  ALTER DATABASE [ DataBaseName ]     MODIFY FILE ( NAME = OldLogicalName ,        NEWNAME =  New LogicalName   ); ALTER DATABASE [ DataBaseName ]       MODIFY FILE  ( NAME = OldLogicalNameLog ,        NEWNAME =  New LogicalNameLog   );
Post a Comment
Read more

See the actual query used by a process

SELECT sqltext . TEXT , req . session_id , req . status , req . command , req . cpu_time , req . total_elapsed_time , req . user_id , req . reads , s . login_name FROM sys . dm_exec_requests req inner join sys . dm_exec_sessions s        on req . session_id = s . session_id  CROSS APPLY sys . dm_exec_sql_text ( sql_handle ) AS sqltext
Post a Comment
Read more

Powershell Commands useful for large CSV files

1) To get the header line, ie: first row in a CSV file  gc .\FILENAME.csv -TotalCount 1 2) To get the header line, and export it to another CSV file.  Note the use of Set-Content rather than Export-CSV gc .\FILENAME.csv -TotalCount 1 | Set-Content .\header-file-name.csv 3) To filter the CSV file based on a regex search , and export the matches to another file. gc .\FILENAME.csv | Select-String -Pattern ( '.*2018-11-20.*' ) | Set-Content .\inner-file-name.csv 4) To see the last few lines, like tail  gc .\20181206_InteractionDetails.csv | select -last 10
Post a Comment
Read more