rtrim based on character

Question

rtrim based on character

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
January 16, 2014 at 10:52 am

#303246

declare @t table
(
s varchar(8000)
)
insert @t
values('Location is Asia and Location is Europe and Location is Africa and '),
('(Location is Europe')
select s
from @t
------------------------------
expected result
------------------------------
Location is Asia and Location is Europe and Location is Africa
Location is Europe

Viewing 9 posts - 1 through 8 (of 8 total)

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

Doug J. SSC Eights! Points: 832 More actions · Answer 1

In your test data, should "('(Location is Europe')" read "('Location is Europe')"? or is that left-parenthesis actually in your data?

-Doug

http://xkcd.com/327/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Shanmuga Raj (1/16/2014)
declare @t table
(
s varchar(8000)
)
insert @t
values('Location is Asia and Location is Europe and Location is Africa and '),
('(Location is Europe')
select s
from @t
------------------------------
expected result
------------------------------
Location is Asia and Location is Europe and Location is Africa
Location is Europe

I can see from what you posted what you are expecting but what are the rules? If the string ends with 'and' you want to remove it? What happens if it is ends with 'sand' or 'something else'? If you can explain the rules we can help you figure out a way to make it work.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 3

declare @t table

(

s varchar(8000)

)

insert @t

values('(Location is Asia)'),

('(Location is Asia and Location is Africa)and Branch in AR and Branch in SE'),

('(Location is Europe)and Branch in AR and Branch in SE'),

('(Type is HO) and (Location is Europe) and Branch in AR and Branch in SE')

select Case when Right(RTRIM(s),1) = 'and Branch in' then

SUBSTRING(s,1,Len(s)-1) else s end

from @t

Kindly help me to frame the Query to Remove text after 'and Branch in'

=================

EXPECTED RESULT

=====================

(Location is Asia)

(Location is Asia and Location is Africa)

(Location is Europe)

(Type is HO) and (Location is Europe)

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

There are a number of ways to accomplish this. Here is one of them.

select case when charindex('and Branch in', s) > 0 then SUBSTRING(s, 0, charindex('and Branch in', s)) else s end

from @t

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 5

I am unable to get result since,

My Table column is having data as stored as below in the nvarchar(max) datatype

---------------------------------------------------------------------------------

'(Type is HO) and

(Location is Europe) and

Branch in AR and Branch in SE'

------------------------------------------------------------------------------------

'(Location is Europe) and

Branch in AR and Branch in SE'

------------------------------------------------------------------------------------------

so, if i use the query

WHEN s LIKE '%and Branch in%'

data is not filtered. Kindly suggest

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Shanmuga Raj (1/16/2014)
I am unable to get result since,
My Table column is having data as stored as below in the nvarchar(max) datatype
---------------------------------------------------------------------------------
'(Type is HO) and
(Location is Europe) and
Branch in AR and Branch in SE'
------------------------------------------------------------------------------------
'(Location is Europe) and
Branch in AR and Branch in SE'
------------------------------------------------------------------------------------------
so, if i use the query
WHEN s LIKE '%and Branch in%'
data is not filtered. Kindly suggest

Do you actually need varchar(max)? How much data is in these columns? varchar can hold 8,000 characters.

Maybe you can try casting your column as varchar(8000)?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 7

My table column datatype is

[nvarchar](max) NULL,

The data is imported from xml, so it is coming in new line with carriage return

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Shanmuga Raj (1/16/2014)
My table column datatype is
[nvarchar](max) NULL,
The data is imported from xml, so it is coming in new line with carriage return

I don't see what varchar(max) and/or this coming from xml has to do with your query. The query as I posted works perfectly fine using varchar(max).

declare @t table

(

s varchar(max)

)

insert @t

values('(Location is Asia)'),

('(Location is Asia and Location is Africa)and Branch in AR and Branch in SE'),

('(Location is Europe)and Branch in AR and Branch in SE'),

('(Type is HO) and (Location is Europe) and Branch in AR and Branch in SE'),

('This one will not be returned.')

select case when charindex('and Branch in', s) > 0 then SUBSTRING(s, 0, charindex('and Branch in', s)) else s end

from @t

where s LIKE '%and Branch in%'

I have a feeling there is something going on here that is lost because your example case is too simplified.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/