CHARINDEX, LEN, and RIGHT - A winning combination for a partial join

Today I needed to join two GUIDs in order to audit login events in a Microsoft CRM database. The problem is that the first block of digits was not the same between the two database tables. 109 more words

Sql Server

Get Hostname from FQDN

When working with a view or table, there may a need to extract the hostname from a fully qualified domain name (FQDN) column value. Below is a case statement that can be used to obtain the hostname. 117 more words


Find index of last occurrence of a sub-string using SQL Server

-- Find Index from the String
select (LEN('0000000000000045') - CHARINDEX('0',Reverse('0000000000000045')) + 1) as FindIndex

-- Using Regx(if after the value is zero(0)
SELECT PATINDEX('%[^0]%','0000000000000450000')

-- Fetch Value except before Zero(0)
SELECT reverse(left(reverse('0000000000000045'), charindex('0', reverse('0000000000000045')) -1)) as FetchValueExceptZero

select REVERSE(SUBSTRING(REVERSE('000000000045'),0,CHARINDEX('0',REVERSE('000000000045')))) as FetchValueExceptZero

-- Using Regx(if after the value is zero(0)
select substring('0000000000000450000',patindex('%[^0]%','0000000000000450000'),Len('0000000000000450000'))
SQL Server

T-SQL String2Date function

I have a private project going on where I’m in the data cleaning and import phase. This weekend’s problem has been a lot of date columns stored as string on 2 different formats (yyyymmdd and ddmmyyyy) and different seperator characters (‘-‘ and ‘.’) all over the place. 92 more words

SQL Server

CHARINDEX function of MS sql server with examples.

Here I would like to explain CHARINDEX function of sql server with examples.
CHARINDEX is used to get starting position of the specified expression in a string. 283 more words

String operations in MSSQL.


This functions return the starting position of a string pattern specified and does not take wildcard character. CHARINDEX takes two arguments, first is the string to find the position and second is the string where the position is to be found. 412 more words


Splitting a range string into a table

This week’s post is a requirement that I see very regularly as a developer. You get a plaintext string containing one or more ranges. Each range is comma delimited, and the start and end values of the range are separated by a dash. 788 more words