how to

How to Remove Leading Whitespace in SQL Server

by

Leading whitespace is a common issue when working with data. A leading whitespace is a space at the start of a string. In most cases, we don’t want any leading whitespace, and we will want to remove it before the data goes any further (whether that means being stored in the database, displayed to the user, or whatever).

Fortunately, SQL Server provides us with the LTRIM() function that allows us to remove leading blanks from a given string.

Read more

2 Ways to Select Rows that Match all Items in a List (T-SQL)

by

This article presents two ways to select rows based on a list of IDs (or other values) in SQL Server. This can be useful in scenarios where you have a comma-separated list of IDs, and you want to query your database for rows that match those IDs.

Say you have the following list of IDs:

1,4,6,8

And so you now want to query a table for records that have any of those values (i.e. either 1, 4, 6 or 8) in its ID column.

Here are two ways to go about that.

Read more

How to Convert a Comma-Separated List into Rows in SQL Server

by

So you have a comma-separated list, and now you need to insert it into the database. But the thing is, you need to insert each value in the list into its own table row. So basically, you need to split the list into its separate values, then insert each one of those values into a new row.

T-SQL now has a STRING_SPLIT() function that makes this type of operation a breeze. This function was first available in SQL Server 2016, and is available on databases with a compatibility level of 130 or above (how to check your database compatibility level and how to change it).

Read more

How to Check a Database’s Compatibility Level in SQL Server using T-SQL

by

In SQL Server, you can use T-SQL to check the compatibility level of a database. All you need to do is query sys.databases to find the compatibility level for the database in question.

Here’s an example:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'WideWorldImporters';

Result:

compatibility_level
-------------------
130

This example returns the compatibility level of the WideWorldImporters database.

Read more

How to Select Everything Before/After a Certain Character in MySQL – SUBSTRING_INDEX()

by

You can use the MySQL SUBSTRING_INDEX() function to return everything before or after a certain character (or characters) in a string.

This function allows you to specify the delimiter to use, and you can specify which one (in the event that there’s more than one in the string).

Syntax

Here’s the syntax:

SUBSTRING_INDEX(str,delim,count)

Where str is the string, delim is the delimiter (from which you want a substring to the left or right of), and count specifies which delimiter (in the event there are multiple occurrences of the delimiter in the string).

Note that the delimiter can be a single character or multiple characters.

Read more

How to Insert a String into another String in SQL Server using STUFF()

by

In SQL Server, you can use the T-SQL STUFF() function to insert a string into another string. This enables you to do things like insert a word at a specific position. It also allows you to replace a word at a specific position.

Here’s the official syntax:

STUFF ( character_expression , start , length , replaceWith_expression )
  • character_expression is the original string. This can actually be a constant, variable, or column of either character or binary data.
  • start specifies the start position (i.e. where the new string will be inserted).
  • length is how many characters are to be deleted from the original string.
  • replaceWith_expression is the string that’s being inserted. replaceWith_expression can be a constant, variable, or column of either character or binary data.

Read more

How to Insert a String into another String in MySQL using INSERT()

by

In MySQL, you can use the INSERT() function to insert a string into another string.

You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with which to insert the new string, the number of characters to delete from the original string, and the new string to insert.

Here’s the syntax:

INSERT(str,pos,len,newstr)

Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert.

Read more