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:…

Read More

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…

Read More

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.…

Read More

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…

Read More

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…

Read More

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…

Read More

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.…

Read More