Find the character index from end

Question

Can someone tell me how to find the character index from the end?

Example:

John(Jk)

I want to find the ( from the end.

Result:

use `charindex()` function to find the char index as `select charindex('(', 'John(Jk)')` — Yogesh Sharma, Jan 08 '18 at 08:14
yogesh charindex() which can able to find from first character. — Aravindhan R, Jan 08 '18 at 08:25
@AravindhanR you could reverse your string and find the char position. — Yogesh Sharma, Jan 08 '18 at 08:26

score 3 · Answer 1 · answered Jan 08 '18 at 08:18

3

One way to rephrase your question is that you want to find the first occurrence of ( from the reversed string.

SELECT
    LEN(col) - CHARINDEX('(', REVERSE(col)) + 1
FROM yourTable;

Demo

In the above demo, the char index of the final ( in the string John(Jk) correctly is determined to be 5.

answered Jan 08 '18 at 08:18

Tim Biegeleisen

Hi Tim Biegeleisen . Can i know why are you using the len-charindex, we can directly use the charindex('(',reverse('jack(crew))) right? – Aravindhan R Jan 08 '18 at 08:31
@AravindhanR If you want the char index as measured from the _start_ of the string, then obviously if you use`CHARINDEX` on the reversed string then the result will (generally) be wrong. My answer converts back to an index of the original string. – Tim Biegeleisen Jan 08 '18 at 08:33
Reverse is not working in teradata. what we can use instead of reverse – Aravindhan R Jan 08 '18 at 09:13
_Teradata_??? You tagged with SQL Server. Changing your database now may draw downvotes. – Tim Biegeleisen Jan 08 '18 at 09:15
I think that most of the query are same that's why i have tag in sql – Aravindhan R Jan 08 '18 at 09:16
You should close out this question and ask a new one for Teradata. – Tim Biegeleisen Jan 08 '18 at 09:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162724/discussion-between-aravindhan-r-and-tim-biegeleisen). – Aravindhan R Jan 08 '18 at 09:18

score 0 · Answer 2 · answered Jan 08 '18 at 08:24

0

Use charindex() with reverse() function to find the index from the end

select charindex('(', reverse(data)) from table

answered Jan 08 '18 at 08:24

Yogesh Sharma

score -1 · Answer 3 · answered Jan 08 '18 at 08:16

-1

You can use CHARINDEX.

Syntax:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Best Regarads!

answered Jan 08 '18 at 08:16

Kevin Böhmer

How do you know where to start searching from the beginning? How does this help the OP? – Tim Biegeleisen Jan 08 '18 at 08:22
I am not intending to spoon-feed anyone here, so i linked a page where you can simply read everything you need to know about Charindex(). Remember that we are not here to solve his homework. – Kevin Böhmer Jan 08 '18 at 08:28