-- 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'))
Tags » CHARINDEX
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
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
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