chopping a string(removing dots)

Question

Post reply

chopping a string(removing dots)

sql_learner29

SSCrazy Eights

Points: 9107
More actions
October 22, 2009 at 9:18 am

#136877

Hi All,
I have a requirement to select from a source which is giving me data in a cloumn like this
ab.cd.ef.gh.kl
hh.jj.kk.rr.ee.bb
ff.gg.tt
Now, I have to select only the values that comes in between 2 dots (removing dots)
:
for this given data,the result should be like this:
cdefgh
jjkkrree
gg
There can be any number of dots in the complete string.
I am not able to find a solution for this.
Any help on this.
Any help is appreciated.
Thanks a lot
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 13 posts - 1 through 12 (of 12 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

So the requirement is to remove all the dots and remove the left 2 chars and the right 2 chars?

Would you like it in SQL or in script?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

SELECT REPLACE('ab.cd.ef.gh.kl','.','')

SELECT REPLACE('hh.jj.kk.rr.ee.bb','.','')

Result:

(No column name)(No column name)

abcdefghklhhjjkkrreebb

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 3

Thanks,

I got the replace to remove dots but how do I remove the first and last characters...

SELECT REPLACE('ab.cd.ef.gh.kl','.','')

SELECT REPLACE('hh.jj.kk.rr.ee.bb','.','')

Result:

(No column name) (No column name)

abcdefghkl hhjjkkrreebb

Result should be:

(No column name) (No column name)

cdefgh jjkkrree

i want only charactr between 2 dots....

And it can be any number of charascters (not just 2)

I can also have a.b.c.d.f.g

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

@Phil,

Yes, but it is not just 2 characters, it can be in any number...

ex:

w.e.r.t

ff.g.h.tr

thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

SELECT SUBSTRING(REPLACE('ab.cd.ef.gh.kl','.',''), 3, len(REPLACE('ab.cd.ef.gh.kl','.',''))-4)

SELECT SUBSTRING(REPLACE('hh.jj.kk.rr.ee.bb','.',''), 3, len(REPLACE('hh.jj.kk.rr.ee.bb','.',''))-4)

You didn't remove the left and right 2 characters 🙂

--edit aaah, just seen the latest post - that's tougher

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246993 More actions · Answer 6

OK, maybe this works:

declare @a varchar(100)

set @a = 'a.b.c.d.f.g'

SELECT REPLACE(SUBSTRING(@a, charindex('.', @a), LEN(@a) - charindex('.', REVERSE(@a))-1), '.','')

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 7

Beat me to it Phil.

Here's what I came up with.

DECLARE @Input varchar(8000)

SELECT @Input = 'ab.cd.ef.gh.kl'

DECLARE @Start int

SELECT @Start = CHARINDEX('.', @Input) + 1

DECLARE @End int

SELECT @End = LEN(@Input) - CHARINDEX('.', REVERSE(@Input)) - @Start + 1

SELECT REPLACE(SUBSTRING(@Input, @Start, @End), '.', '')

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 8

Thanks a lot... Phil..Erik

it works....... 😎

I have one more question,

Is there any way to split this long string into columns partitioned by DOTS

For Ex:

ab.cd.ef.gh

gives

Col1 Col2 Col3 Col4

ab cd ef gh

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Phil Parkin SSC Guru Points: 246993 More actions · Answer 9

Erik Kutzler (10/22/2009)
Beat me to it Phil.
<snip>

Same logic ... must be good! 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246993 More actions · Answer 10

SQL Learner-684602 (10/22/2009)
Thanks a lot... Phil..Erik
it works....... 😎
I have one more question,
Is there any way to split this long string into columns partitioned by DOTS
For Ex:
ab.cd.ef.gh
gives
Col1 Col2 Col3 Col4
ab cd ef gh
Thanks

Sure. Define '.' as the column delimiter and let SSIS do all the splitting work for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 11

Ohkk..Yes but I am using a Stored Procedure and have to do it in a select query...

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

SQL Learner-684602 (10/22/2009)
Ohkk..Yes but I am using a Stored Procedure and have to do it in a select query...

Once again, this topic does not belong in the SSIS section. In the future please post your questions in the correct forum sections. Doing so should results in better answers.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]