query delete some part of string

Question

query delete some part of string

neliii

Say Hey Kid

Points: 701
More actions
May 7, 2010 at 11:12 am

#234820

column1
\\xyz.se.df.a.b\fgd\
colum2
\\xyz\fgd\
now i want to join both the coulmns by elimating the highlighted part. i mean .se.df.a.b
or else just give me the query which can elimante that part from that string

Viewing 15 posts - 1 through 15 (of 18 total)

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

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

neliii (5/7/2010)
column1
\\xyz.se.df.a.b\fgd
colum2
\\xyz\fgd
now i want to join both the coulmns by elimating the highlighted part. i mean .se.df.a.b
or else just give me the query which can elimante that part from that string

I am not sure if this is what you want, but it is what I read.

UPDATE YourTable

SET Column1 = Column2

WHERE YourWhereClause

-- Cory

neliii Say Hey Kid Points: 701 More actions · Answer 2

i need a query to eliminate that highlited part of the string so that after elimination it looks like the row in colum2

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

My query will do that. you want col1 to be the same as col2.

-- Cory

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

neliii (5/7/2010)
column1
\\xyz.se.df.a.b\fgd
colum2
\\xyz\fgd
now i want to join both the coulmns by elimating the highlighted part. i mean .se.df.a.b
or else just give me the query which can elimante that part from that string

Do you mean, something like:

select replace ( '\\xyz.se.df.a.b\fgd\' , '.se.df.a.b' , '' )

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

neliii Say Hey Kid Points: 701 More actions · Answer 5

@cory

Thankq cory

i got it.

And for now lets forget about column2

i jus want to eliminate that part of string. Can u help me with query whihc will eliminate that part.

I really appriciate ur help for me

Ben Teraberry SSChampion Points: 11845 More actions · Answer 6

Here is an example to get rid of everything after the first "." and before the third "\". You provided very little info, so I don't know if that's exactly what you're wanting to do but it was my best guess.

select stuff('\\xyz.se.df.a.b\fgd\', charindex('.', '\\xyz.se.df.a.b\fgd\'), charindex('\', '\\xyz.se.df.a.b\fgd\', 3) - charindex('.', '\\xyz.se.df.a.b\fgd\') - 1, '')

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

Actually, I should do it like this so it may be more clear:

declare @vc_string varchar(20);

select @vc_string = '\\xyz.se.df.a.b\fgd\';

select stuff(@vc_string, charindex('.', @vc_string), charindex('\', @vc_string, 3) - charindex('.', @vc_string) - 1, '');

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

neliii Say Hey Kid Points: 701 More actions · Answer 8

@pablo-2

Thanks for ur help

what if i got different rows in that column like

example:

Colum1

\\xyz.ed.kl.e.a\fgd\\rsq.fd.ij.g.b\fgd\\wes.as.gh.c.b\fgd\\abc.er.tf.h.d\fgd

what if i want to get the rows with the part of the string starting from like .ed.kl.e.a i mean starting from . to \ eliminated in all the rows.

Cory E. SSCertifiable Points: 7076 More actions · Answer 9

DECLARE @STR NVARCHAR(30)

SET @STR = '\\xyz.ed.kl.e.a\fgd\'

SELECT SUBSTRING(@str, 1, CHARINDEX('.', @STR) - 1) + SUBSTRING(@str, CHARINDEX('\', @STR, 3), 4)

, SUBSTRING(@str, 1, CHARINDEX('.', @STR) - 1) + SUBSTRING(@str, CHARINDEX('\', @STR, CHARINDEX('.', @STR) - 1), 4)

My result for the one test is:

\\xyz\fgd

-- Cory

Ben Teraberry SSChampion Points: 11845 More actions · Answer 10

Mine was more efficient. Plus STUFF is simply cool and underused.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

GregoryF SSCertifiable Points: 6296 More actions · Answer 11

bteraberry (5/7/2010)
Mine was more efficient. Plus STUFF is simply cool and underused.

Props on the STUFF, in 12 years of doing SQL, that's the first time I have ever seen it used except in BOL/MSDN examples.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Ben Teraberry SSChampion Points: 11845 More actions · Answer 12

GregoryF (5/7/2010)
bteraberry (5/7/2010)
Mine was more efficient. Plus STUFF is simply cool and underused.
Props on the STUFF, in 12 years of doing SQL, that's the first time I have ever seen it used except in BOL/MSDN examples.

Heh, thanks.

And here is the whole example:

declare @t_temp table

(Column1 varchar(50));

insert into @t_temp

select '\\xyz.ed.kl.e.a\fgd\' union

select '\\rsq.fd.ij.g.b\fgd\' union

select '\\wes.as.gh.c.b\fgd\' union

select '\\abc.er.tf.h.d\fgd\';

/* and the actual function ... */

select stuff(Column1, charindex('.', Column1), charindex('\', Column1, 3) - charindex('.', Column1) - 1, '')

from @t_temp;

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Cory E. SSCertifiable Points: 7076 More actions · Answer 13

Thanks! I have used stuff once or twice - you are correct - underused!

-- Cory

neliii Say Hey Kid Points: 701 More actions · Answer 14

@ all

thank you guys..

i got it..

but i got one more qstn

i got another row in that

\\xyz.ed.kl.e.a\fgd\\rsq.fd.ij.g.b\fgd\\wes.as.gh.c.b\fgd\\abc.er.tf.h.d\fgd\\eds\fgd

now when i'm trying to print the result for all the rows after eliminationg the string after . to

my result is like

\\xyz\fgd\\rsq\fgd\\wes\fgd\\abc\fgdNULL

the last row is getting as null

but i want the result to be

\\xyz\fgd\\rsq\fgd\\wes\fgd\\abc\fgd\\eds\fgd

Can u help me out with that ASAP

Thanq In advance