?
Solved

How to get some text based on a char

Posted on 2014-01-13
5
Medium Priority
?
297 Views
Last Modified: 2014-01-13
Hi there

Hope you can help me?

I have a string like this

Fred Blog (Feb 2009)

Can someone please write a function that will get me ALL the text BEFORE the '('

i.e.

Fred Blog

thanks
0
Comment
Question by:mousemat24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 400 total points
ID: 39777086
Try this:

SELECT LEFT(ColumnName,CHARINDEX('(', ColumnName)-1)

Open in new window

0
 

Author Comment

by:mousemat24
ID: 39777089
can you please help me so that I can use it as a function?

thanks
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1600 total points
ID: 39777094
Here is the function:
CREATE FUNCTION GetText(@Text VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
	RETURN SUBSTRING(@Text, 1, CHARINDEX('(', @Text) - 1)
END

Open in new window


SELECT dbo.GetText('Fred Blog (Feb 2009)')

Open in new window

0
 
LVL 14

Expert Comment

by:Michael Dyer
ID: 39777098
Here you go.

Assume you have a table named "MyTable"

Create one column named "INUPT" for your input string and one column named "NAME" for the trimmed name.

Then, run this procedure on the table:

     UPDATE MyTable SET NAME = LEFT(INPUT,LEN(INPUT)-CHARINDEX('(',INPUT))

This should put the trimmed name in the NAME column.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39777105
You have answers above, but here are a few notes:

Your substring will end up with an extraneous space; therefore, you may need to RTRIM the text if there is a space before '(' as in your example.
If the text you pass the function does not contain a '(', you will get an error; therefore, you may want to add an IF/CASE statement OR do this:
SUBSTRING(@Text, 1, CHARINDEX('(', @Text+'(') - 1)

Open in new window

0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question