Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to get some text based on a char

Posted on 2014-01-13
5
Medium Priority
?
302 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

609 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