trim from character right 1

[dnfrantum]

How would I trim from a character right? Example:

6213 - Help a brotha out(please)

I want to trim everything from ( right, leaving

6213 - Help a brotha out

Thanks in advance,
Donald

 

[RiverGuy]

DECLARE @STRING VARCHAR(100)
SELECT @STRING = '6213 - Help a brotha out(please)'

SELECT LEFT(@STRING, CHARINDEX('(', @STRING, 1) - 1)

 

[SgtJarrow]

You can leverage SUBSTRING and CHARINDEX

DECLARE	@str VARCHAR(50)

SET @str = '6213 - help a brotha out(please)'

SELECT SUBSTRING(@str, 0, CHARINDEX('(', @str))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[dnfrantum]

Do I always have to do declare? what if '6213 - Help a brother out(please)' is a field_a in the table?

I am using

SELECT LEFT(T0.AcctName, CHARINDEX('(', T0.AcctName, 1) - 1)
FROM mytable T0

and I am getting the error:

Invalid length parameter passed to the SUBSTRING function.

Thanks in advance,
Donald

 

[gmmastros]

This should fix the substring error:

SELECT LEFT(T0.AcctName, CHARINDEX('(', T0.AcctName [!]+ '('[/!], 1) - 1)
FROM mytable T0

It was caused by data that does not contain an open parenthesis. CharIndex returns 0, you subtract -1 and end up with Left(Blah, -1).

make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

The variable was to provide a working example. Substitute it with your column.

It appears that some of your columns don't have a "(". In that case, use the following:

SELECT 
CASE 
	WHEN CHARINDEX('(', T0.AcctName, 1) > 0 THEN LEFT(T0.AcctName, CHARINDEX('(', T0.AcctName, 1) - 1)
	ELSE T0.AcctName
END
FROM mytable T0

 

[gmmastros]

I guess that's another way.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

George's way is simpler

 

[dnfrantum]

I agree. Thanks everyone; it worked.

Thanks in advance,
Donald