Like to sort records that have only one"_" charact

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Like to sort records that have only one"_" charact

Author

Topic

kvkanth008
Starting Member

1 Post

Posted - 2009-02-26 : 07:52:10

01 VarunK Sr_Test_Engineer
02 Raghu Sr_Quality_Control_Enginr
03 Auditya Sr_Quality_Analyst_Engr.
04 Prabhakar Test_Engineer
05 Hari Test_Engineer
06 CVRSwamy Test_Engineer
07 Tirumalesh Test_Engineer

In the above table, I would like to fetch records that have only one under score(_). How can I do this, Pls Help me out.

Kolvi

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-26 : 08:02:56

does this work ?

select * from <yourtable>
where len(col)-charindex('_',reverse(col))+1=charindex('_',col)

replace <yourtable> with your table name, and col with the column name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 04:56:31

or

where len(col)-len(replace(col,'_',''))=1

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-02 : 07:16:01

Hi try this one also

declare @temp table (id int, name varchar(32), desig varchar(32))
insert into @temp
select 01 ,'VarunK', 'Sr_Test_Engineer' union all
select 02 ,'Raghu', 'Sr_Quality_Control_Enginr' union all
select 03 ,'Auditya', 'Sr_Quality_Analyst_Engr' union all
select 04 ,'Prabhakar', 'Test_Engineer' union all
select 05 ,'Hari', 'Test_Engineer' union all
select 06 ,'CVRSwamy' ,'Test_Engineer' union all
select 07 ,'Tirumalesh' ,'Test_Engineer'

select * from @temp where parsename(replace(desig,'_','.'),3) is null

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 08:01:31

quote:
Originally posted by Nageswar9

Hi try this one also

declare @temp table (id int, name varchar(32), desig varchar(32))
insert into @temp
select 01 ,'VarunK', 'Sr_Test_Engineer' union all
select 02 ,'Raghu', 'Sr_Quality_Control_Enginr' union all
select 03 ,'Auditya', 'Sr_Quality_Analyst_Engr' union all
select 04 ,'Prabhakar', 'Test_Engineer' union all
select 05 ,'Hari', 'Test_Engineer' union all
select 06 ,'CVRSwamy' ,'Test_Engineer' union all
select 07 ,'Tirumalesh' ,'Test_Engineer'

select * from @temp where parsename(replace(desig,'_','.'),3) is null

Wont work for all set of data


declare @temp table (id int, name varchar(32), desig varchar(32))
insert into @temp
select 01 ,'VarunK', 'Sr_Test_Engineer' union all
select 02 ,'Raghu', 'Sr_Quality_Control_Enginr' union all
select 03 ,'Auditya', 'Sr_Quality_Analyst_Engr' union all
select 04 ,'Prabhakar', 'Test_Engineer' union all
select 05 ,'Hari', 'Test_Engineer' union all
select 06 ,'CVRSwamy' ,'Test_Engineer' union all 
select 07 ,'Tirumalesh' ,'Test_Engineer_Test_Engineer_' 

select * from @temp where parsename(replace(desig,'_','.'),3) is null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources