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