CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3383390
user3383390

Reputation: 211

How to extract Certain nth character from a string in SQL

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.

Upvotes: 13

Views: 98396

Answers (5)

Leo Zhang
Leo Zhang

Reputation: 1

the following code will return the nth substring after delimiting, either from left or from right; in the particular example the delimiter is comma, and it is looking for the 2nd segment, counting from right

declare

@str_orig VARCHAR(256)='123,345,8,6,7',
@delimit varchar(50) =',',
@loc int =2,
@from_left bit =0



SELECT value, row_number()  over( order by( select 1)) as row_num
into #split
FROM STRING_SPLIT(@str_orig, @delimit)

declare @result varchar(256)
if @from_left=1
 begin
 set @result = ( select top 1  value  from  #split  where row_num=@loc)
 end
else
 begin

 set @result = ( select top 1  value  from  #split
 where row_num= (SELECT MAX(ROw_num) -(@loc-1) FROM  #split)
 )
 
 end


select @result

Upvotes: 0

Joseph B
Joseph B

Reputation: 5669

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).

Upvotes: 29

mohan111
mohan111

Reputation: 8865

declare @x varchar(20) = 'xxx-xxx-xxx-xxxxx-xx-x'

select SUBSTRING(@x,10,CHARINDEX('-',@x)-4 )

Upvotes: 0

G one
G one

Reputation: 2729

Use substring function

select substring('xxx-xxx-xax-xxxxx-xx-x', 10, 1)

Upvotes: 6

Christian Phillips
Christian Phillips

Reputation: 18749

you can use SUBSTRING, in your case use...

SELECT SUBSTRING(field, 10, 1)

field being the one that returns the value.

Upvotes: 5

Related Questions