to find specific position string

Question

Post reply

to find specific position string

asranantha

SSCarpal Tunnel

Points: 4246
More actions
April 25, 2013 at 2:26 am

#293029

Hi friends i have small doubt in sql server ,
table data contains like
id , name
1 , srinivas
2 , ravikumar
3 , jaipal
4 , ravisekhar
5 , ramugopal
6 , harikuma
based on this table data i want display 3rd positon charcter only
output like
id , name
1 , v
2 , m
3 , p
4 , h
5 , p
6 ,u
actualy i try query like select id,substring(name,3,1) from emp
but its not give exactely result.
plese tell me how to write query go get this result.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

;with SCC_cTE(ID ,Name)

AS (SELECT 1, 'srinivas' union all

select 2, 'ravikumar' union all

select 3, 'jaipal' union all

select 4, 'ravisekhar' union all

select 5, 'ramugopal' union all

select 6, 'harikuma')

select id, name, SUBSTRING(REVERSE(name), 3, 1) as output

from SCC_cTE

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

davidandrews13 SSCertifiable Points: 6820 More actions · Answer 2

substring reads from Left to Right, not Right to Left as some Languages from non English speaking countries might.

so when you put '3' in to your substring, your asking for the 3rd character from the left.

;with cte(id , name) as (

select 1 , 'srinivas'

union select 2 , 'ravikumar'

union select 3 , 'jaipal'

union select 4 , 'ravisekhar'

union select 5 , 'ramugopal'

union select 6 , 'harikuma'

)

select id,SUBSTRING(name,LEN(name) - 2,1) from cte

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

DECLARE @string varchar(10) = 'aapail'

SELECT SUBSTRING(REVERSE(@string),3,1)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

chalam87 Right there with Babe Points: 771 More actions · Answer 4

hi,

before finding sub string you should trim the data otherwise it takes empty data also.

select id,SUBSTRING(Reverse(LTRIM(RTRIM(name))),3,1) from emp

--chalam

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

chalam87 (4/25/2013)
hi,
before finding sub string you should trim the data otherwise it takes empty data also.
select id,SUBSTRING(Reverse(LTRIM(RTRIM(name))),3,1) from emp

;WITH Sampledata (id, name) AS (

SELECT 1 , 'srinivas ' UNION ALL

SELECT 2 , 'ravikumar ' UNION ALL

SELECT 3 , 'jaipal' UNION ALL

SELECT 4 , ' ravisekhar' UNION ALL

SELECT 5 , ' ramugopal' UNION ALL

SELECT 6 , ' harikuma ' )

-- LTRIM() isn't necessary:

SELECT

id,

name,

NameShowingPadding = '|' + name + '|',

Fails = SUBSTRING(REVERSE(name),3,1),

Oneway = SUBSTRING(REVERSE(RTRIM(name)),3,1),

Anotherway = LEFT(RIGHT(RTRIM(name),3),1)

FROM Sampledata

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

ChrisM@Work (4/25/2013)
chalam87 (4/25/2013)
hi,
before finding sub string you should trim the data otherwise it takes empty data also.
select id,SUBSTRING(Reverse(LTRIM(RTRIM(name))),3,1) from emp
;WITH Sampledata (id, name) AS (
SELECT 1 , 'srinivas ' UNION ALL
SELECT 2 , 'ravikumar ' UNION ALL
SELECT 3 , 'jaipal' UNION ALL
SELECT 4 , ' ravisekhar' UNION ALL
SELECT 5 , ' ramugopal' UNION ALL
SELECT 6 , ' harikuma ' )
-- LTRIM() isn't necessary:
SELECT
id,
name,
NameShowingPadding = '|' + name + '|',
Fails = SUBSTRING(REVERSE(name),3,1),
Oneway = SUBSTRING(REVERSE(RTRIM(name)),3,1),
Anotherway = LEFT(RIGHT(RTRIM(name),3),1)
FROM Sampledata

Good one Chris 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/