select query resultset between hyphens

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 2008 Forums

Transact-SQL (2008)

select query resultset between hyphens

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-28 : 10:40:49

I have the following, want to get all projects based on "DS".

"DS" is in the middle part of project names, between hyphens.

declare @Sample table (Proj_name varchar(100))
insert @Sample
select 'SPIA 1 - AJ - ASESORIA JURIDICA' union all
select 'EQN 1 - DS - DERECHO SUPERFICIARIOS' union all
select 'PTB 1 - DS - DERECHO SUPERFICIARIOS' union all
select 'ENS 1 - AJ - ASESORIA JURIDICA' union all
select 'VET 1 - GS - DERECHOS SUPERFICIARIOS' union all
select 'VET 2 - GS - DERECHOS SUPERFICIARIOS' union all
select 'VET 3 - DS - DERECHOS SUPERFICIARIOS' union all
select 'VET 4 - DS - DERECHOS SUPERFICIARIOS' union all
select 'COC 1 - DS - DERECHOS SUPERFICIARIOS' union all
select 'COC 2 - AJ - ASESORIA JURIDICA' union all
select 'PEL 1 - DS - DERECHOS SUPERFICIARIOS'

thank you very much for the helpful info.

Lewie
Starting Member

42 Posts

Posted - 2011-09-28 : 10:42:42

select *
from @Sample
WHERE replace(left(substring(Proj_name,CHARINDEX('-',Proj_name,1) + 1,LEN(Proj_name) - CHARINDEX('-',Proj_name,1) ) ,
CHARINDEX('-',substring(Proj_name,CHARINDEX('-',Proj_name,1) + 1,LEN(Proj_name) - CHARINDEX('-',Proj_name,1))) - 1),' ','')
= 'DS'

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-28 : 11:01:57

Lewie,
I am getting this error message:

select replace(left(substring(projname,CHARINDEX('-',projname,1) + 1,LEN(projname) - CHARINDEX('-',projname,1) ) ,
CHARINDEX('-',substring(projname,CHARINDEX('-',projname,1) + 1,LEN(projname) - CHARINDEX('-',projname,1))) - 1),' ','')
from tab_projects

Msg 537, Level 16, State 3, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:18:00

replace CHARINDEX('-',projname,1) with CHARINDEX('-',projname+'-',1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-28 : 11:21:49

thank you Visakh, i replaced as per your suggestion. still i am getting this error:

select replace(left(substring(projname,CHARINDEX('-',projname+'-',1) + 1,LEN(projname) - CHARINDEX('-',projname+'-',1) ) ,
CHARINDEX('-',substring(projname,CHARINDEX('-',projname+'-',1) + 1,LEN(projname) - CHARINDEX('-',projname+'-',1))) - 1),' ','')
from tab_ccsnetprojects

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-28 : 11:27:35

Visakh,

I have some rows with projname which doesn't have the same format, is it possible to exclude thsoe rows which doesn't have two hyphen format?

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:48:44

will it be maximum of 2 hyphens?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-28 : 11:56:07

Yes, visakh.

between the two hyphens.

but there coul be rows, which may not at all have those hyphen formats, in that case the select query should exclude those rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:59:03

[code]
SELECT PARSENAME(REPLACE(Proj_name,' - ','.'),2) AS ReqdField
FROM Table
WHERE LEN(Proj_name)-LEN(REPLACE(Proj_name,' - ',''))=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lewie
Starting Member

42 Posts

Posted - 2011-09-30 : 09:42:43

select *
from (Select * from @Sample where CHARINDEX('-',Proj_name,1) <> 0) z
WHERE replace(left(substring(Proj_name,CHARINDEX('-',Proj_name,1) + 1,LEN(Proj_name) - CHARINDEX('-',Proj_name,1) ) ,
CHARINDEX('-',substring(Proj_name,CHARINDEX('-',Proj_name,1) + 1,LEN(Proj_name) - CHARINDEX('-',Proj_name,1))) - 1),' ','')
= 'DS'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources