String Extraction - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

String Extraction

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-04-21 : 07:28:35

Dear All

I just need to extrct the first part of the sting

cstNumber
4345234_refund_323445646
ETI123456_ssued
45634579_refund

Result
4345234
ETI123456
45634579

How to do this.Any help would be highly appreciated.Thanks.

Regards,
SG

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 07:38:21

left(str,charindex('_',str)-1)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 07:38:57

[code]
select
case when charindex('_',cstNumber) > 0 then
stuff(cstNumber,charindex('_',cstNumber), len(cstNumber),'')
else
cstNumber
end
from
YourTable[/code]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 07:39:29

Just like you asked yesterday: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159704

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-21 : 07:39:58

What is the matter?
Your question was already answered yesterday:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159704

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-21 : 07:40:32

oh boy!!

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 07:41:16

Four replies in 1 minute - what an active forum!! What is everyone doing up so early!?!?!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-21 : 07:42:09

early?
It is 13:40 here

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

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-21 : 07:44:43

Try this
create table #t (cstNumber varchar(max))
insert into #t
select '4345234_refund_323445646' union all
select 'ETI123456_ssued' union all
select '45634579_refund'
--Run this
select substring(cstNumber,1,charindex('_',cstNumber)-1) from #t

Raghu' S

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 07:44:51

I just watched Enemy at the Gates a few days ago, so my sniping skills have improved lately.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 07:46:20

Heh! of course!! For a moment, I forgot.

Sometimes I think of the regular people on this forum as though I know them in person and that we are all sitting in the same office building in offices down the hall or across the hall.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-21 : 07:47:57

quote:
Sometimes I think of the regular people on this forum as though I know them in person and that we are all sitting in the same office building in offices down the hall or across the hall.

We do! The office is called SQLTeam!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 07:50:22

People sleep?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-21 : 07:53:34

Then lets we meet some where!

Raghu' S

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-21 : 08:03:10

quote:
Originally posted by sunitabeck

Sometimes I think of the regular people on this forum as though I know them in person and that we are all sitting in the same office building in offices down the hall or across the hall.

Oh, I know that feeeling too

And it is a good feeling!

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 08:22:15

quote:
Originally posted by webfred

quote:
Originally posted by sunitabeck

Sometimes I think of the regular people on this forum as though I know them in person and that we are all sitting in the same office building in offices down the hall or across the hall.

Oh, I know that feeeling too
And it is a good feeling!

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

Earlier this year, the company I worked for folded (That seems to be a pattern with me - I am able to single-handedly bring down companies I work for by doing nothing more than working there). So I was looking for a new job, but jobs were hard to come by (To console myself, I kept telling myself that the reason for my plight is my poor interviewing skills and not my lack of technical skills).

Any way, so then I got this job offer with a startup company. The catch was that I would have to be the system administrator as well. In my desperation, I SERIOUSLY thought of taking that job even though I don't know the first things about SQL server administration. My rationale was that if I am in a bind, I can always ask on SQLTeam and someone would come to the rescue. I was picturing Rob or Tara or Gail patiently trying to walk me through recovering a corrupt master database.

Thankfully I got another job where I just have to be just a T-SQL developer with a sprinkling of C#, Perl etc. which I feel more comfortable with.

So, yes it is a good feeling to know that SQLTeam is here!!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 08:38:38

Congratulations on the new job! When do you start?

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 08:40:31

Thank you!!!

Proud to report that currently I am gainfully employed - started two weeks ago.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources