Menu
I have a field that returns the value as xxx-xxx-xxx-xxxxx-xx-x. How do i extract the 10th character from that code.
950
T-SQL's CHARINDEX() function is useful for parsing out characters within a string. However, it only returns the first occurrence of a character. Over at SQL Server Central, there is a function that Cade Bryant wrote that returns the Nth occurrence of a character.
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column. SELECT LEN(column_name) FROM table_name; And you can use SUBSTRING or SUBSTR() function go get first three characters of a column.
select substring('xxx-xxx-xxx-xxxxx-xx-x', 10, 1)
The documentation for the function on MSDN is here.
The SQL Fiddle demo is here (with different letters so you can clearly see which letter is extracted).
64
Use substring function
select substring('xxx-xxx-xax-xxxxx-xx-x', 10, 1)
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With