I have created a simple C# console application as a tool to split big SQL scripts. For example, I had a problem with data seed scripts.
Category: SQL
How do I extract text that lies between two strings?
My initial problem was to extract from a SQL string generated by MySql, all fields. I had a string like 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 INSERT INTO `inventoryapp`.`inventory_keys` (`Id`, `PropertyId`, `AppointmentId`, `SectionType`, `KeysDescription`, `FobsWorking`, `EntryCodes`, `AlarmCodes`, `Notes`, `Version`, `CreatedDate`, `CreatedBy`, `UpdatedDate`, `UpdatedBy`, `IsDeleted`) VALUES (<{Id: }>, <{PropertyId: }>, <{AppointmentId: }>, <{SectionType: }>, <{KeysDescription: }>, <{FobsWorking: }>, <{EntryCodes: }>, <{AlarmCodes: }>, <{Notes: }>, <{Version:…
Migrate SQLite to MySQL
I’m developing a huge app made with Xamarin Forms in my company. A problem was to create all tables in the device with SQLite to the main database MySql. I found a way to create all tables quickly with a tool called SQLite to MySQL. SQLite-to-MySQL is a powerful and reliable tool to convert SQLite databases to MySQL, MariaDB or Percona format. The program has high performance due to direct connection to source and destination databases (it does not use ODBC or any other middleware software). Command line support allows…
How to use REPLACE() within NTEXT columns in SQL Server
SQL has an incredibly useful function, REPLACE(), which replaces all occurrences of a specified string with another string, returning a new string. It works great with all forms of NCHAR and NVARCHAR fields. It does not, however, work with NTEXT fields. Fear not — there’s an easy workaround, thanks to type-casting and SQL 2005’s NVARCHAR(max) datatype. Here’s the process in an nutshell. Cast the NTEXT field to the NVARCHAR(max) datatype using the CAST function. Perform your REPLACE on the output of #1. Cast the output of #2 back to NTEXT.…
How can I know when SQL Full Text Index Population is finished?
When you execute some queries on your SQL Server, are you sure the catalog is being imported? With this simple script you can know which is the Index status. DECLARE @CatalogName VARCHAR(MAX) SET @CatalogName = ‘TEST_FullIndex’ SELECT FULLTEXTCATALOGPROPERTY(@CatalogName,’ItemCount’) as NumberOfItems, FULLTEXTCATALOGPROPERTY(@CatalogName,’ImportStatus’) as ImportStatus, DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName, ‘PopulateCompletionAge’), ‘1/1/1990′) AS LastPopulated, (SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,’PopulateStatus’) WHEN 0 THEN ‘Idle’ WHEN 1 THEN ‘Full Population In Progress’ WHEN 2 THEN ‘Paused’ WHEN 3 THEN ‘Throttled’ WHEN 4 THEN ‘Recovering’ WHEN 5 THEN ‘Shutdown’ WHEN 6 THEN ‘Incremental Population In Progress’ WHEN 7 THEN ‘Building…
How to remove HTML tags from data with SQL
The purpose of this article is to provide a way of cleaning up of HTML tags within the data. When we use various styles or tabular format data in UI using Rich Text Editor/ Rad Grid etc, it will save data in database with HTML tags. When you save in database this kind of field you have: An HTML element starts with a start tag (<p>) and ends with end tag (<p/>) and everything between Start tag and End tag is HTML element. e.g. <b>Following are the popular databases: <br…
SQL SERVER – Find Current Location of Data and Log File of All the Database
As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary files I have left there taking up my hard drive space. I run following query to find out which .mdf and .ldf files are used and delete all other files. If your SQL Server is up and running OS will…
How to truncate log file in SQL Server
In SQL Server data is stored using two physical files: (.mdf) extension which contains the data. (.ldf) extension which contains log. Log file size increases very rapidly and depend on the operation performed on the data. After a long time period this file becomes too large. When log file is too large it takes time to perform some operations like (attach, de-attach, backup, restore… etc ). Step 1. Copy/type the below given SQL. Step 2. Change @DBName to <Database Name>, @DBName_log to <Log File Name> Step 3. Execute the SQL.…