Tags » CHARINDEX

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

SQL

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.

CHARINDEX:

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

MSSQL

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

T-SQL

Removing part of string before and after specific character using Transact-SQL string functions

Problem

Today, one of the developers come to me and asked me the question that is there any T-SQL function that he could use to remove everything before and after a specific character in string. 210 more words

SQL Scripts