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)
 Substring after second "_" string length varies

Author  Topic 

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 06:59:58
Good day, I need help.

I have a difficult one.
I have a filed that returns a unique values but length chages & i only want the last part of the string values after the second "_"

Field: ul_633948394629672802_35_Copy of Test1.xls
ul_633948409351423738_2_Copy of Test3New Item Test.xls

the position of the second "_" changes but it always one of the 2 positions being 2 or 1 char in front of it.

Then i want to return the part therafter: Copy of Test1.xls & Copy of Test3New Item Test.xls

Please help - no clue on how to do this

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-11-30 : 07:07:14
You can use substring(), reverse() and charindex() and again reverse() to the part of the string from the end to the FIRST underscore.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 07:09:17
Thank You for assisting,

can you please provide code :(
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 07:13:17
[code]
select data,right(data,charindex('_',reverse(data))-1) as [filename] from
(
select 'ul_633948394629672802_35_Copy of Test1.xls' as data union all
select 'ul_633948409351423738_2_Copy of Test3New Item Test.xls'
) as t
[/code]


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 07:16:23
is there a way to do it without the union
becuase i already have a big statement & this forms part of it & can't do a union for the rest of the query!
only one select where i can slot it in my current query

Regards
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 07:18:03
quote:
Originally posted by ismailc

is there a way to do it without the union
becuase i already have a big statement & this forms part of it & can't do a union for the rest of the query!
only one select where i can slot it in my current query

Regards

Post the query you have

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 07:24:04
IF EXISTS (select * from dbo.fcEventArchive where ProcessID = @ProcessNo)
select MAX(Case when v.ControlID = 'VE1020User' then v.ControlValue else '' END) as I_User
,MAX(Case when v.ControlID = 'VE1040Current' then v.ControlValue else '' END) as I_CurrentSupp
,MAX(Case when v.ControlID = 'VE10FileINTCreditApp' then v.ControlValue else '' END) as I_INTCreditApp
etc
from dbo.fcEventArchive as e
left join dbo.fcEventValueArchive as v
on v.EventHistID = e.EventHistID
and v.ControlID in ('VE1020User', 'VE1040Current', etc)
join (select max(v1.EventHistID) as max_id,v1.ControlID from dbo.fcEventArchive E1
left join dbo.fcEventValueArchive V1 on E1.EventHistID = V1.EventHistID where E1.ProcessID = @ProcessNo group by v1.controlid) max_qry
on max_id = V.EventHistID and max_qry.ControlID = V.ControlID
where e.ProcessID = @ProcessNo

Else

select MAX(Case when v.ControlID = 'VE1020User' then v.ControlValue else '' END) as I_User
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 07:30:10
What is the column that you want to apply if for?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 07:44:13
,MAX(Case when v.ControlID = 'VE10FileINTCreditApp' then v.ControlValue else '' END) as I_INTCreditApp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 07:51:52
,MAX(Case when v.ControlID = 'VE10FileINTCreditApp' then right(v.ControlValue,charindex('_',reverse(data))-1) else '' END) as I_INTCreditApp

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 08:01:45
,MAX(Case when v.ControlID = 'VE1090INTCreditApp' then right(v.ControlValue,charindex('_',reverse(data))-1) else '' END) as I_INTCreditApp2

error: invlaid column name: data

Please help
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 08:03:01
quote:
Originally posted by ismailc

,MAX(Case when v.ControlID = 'VE1090INTCreditApp' then right(v.ControlValue,charindex('_',reverse(data))-1) else '' END) as I_INTCreditApp2

error: invlaid column name: data

Please help

,MAX(Case when v.ControlID = 'VE1090INTCreditApp' then right(v.ControlValue,charindex('_',reverse(v.ControlValue))-1) else '' END) as I_INTCreditApp2

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-11-30 : 08:13:24
Hi madhi, you are able so often to simplify my given approaches.
Just to use right() instead of handling substring() - I was blind...

cool man!

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 08:23:31
Great stuff - Thank You...

it works & i'm gratefull for the help.

one ques: what happens if the filename has an "_" within the file name.

eg: ul_633948394629672802_35_Copy of_Test1.xls

I think the chances are slim but curious :)
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-11-30 : 08:29:10
You have given the rule to extract the part of the string.
If that rule is not correct then you have to give a better rule or to live with the results...

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 08:33:52
I'm just curious as i said the chances of it happening slim.

Thank You very much :)
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 08:59:50
Hi, i get an error invalid string legth specified.

i get this error when there is no value returned, value = ''

,MAX(Case when v.ControlID = 'VE10130EXTVendorApp' then right(v.ControlValue,charindex('_',reverse(v.ControlValue))-1) else '' END) as I_EXTVendorApp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 09:04:00
,MAX(Case when v.ControlID = 'VE10130EXTVendorApp' then right(v.ControlValue,charindex('_',reverse(v.ControlValue))-case when charindex('_',reverse(v.ControlValue))>0 then 1 else 0 end) else '' END) as I_EXTVendorApp

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 09:04:42
okay now, added to check for '' in case

and v.ControlValue <> ''
Go to Top of Page

ismailc
Constraint Violating Yak Guru

290 Posts
Posted - 2009-11-30 : 09:05:49
Thank You very much :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-11-30 : 09:05:55
quote:
Originally posted by webfred

Hi madhi, you are able so often to simplify my given approaches.
Just to use right() instead of handling substring() - I was blind...

cool man!

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site