String Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String Function

Author

Topic

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 17:56:10

Hello All:

I'm trying to select all the characters that appear before a hyphen. For example, I'd like to retrieve 'Sports' from a field with the value 'Sports - rating'

Any help!? I've tried everything from ltrim to leading...

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 18:03:12

DECLARE @s varchar(50)

SET @s = 'Sports - rating'

SELECT SUBSTRING(@s, 1, CHARINDEX('-', @s) - 1)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 18:13:40

'Sports-rating' was just an example...I'm needing to select the characters before and after the hyphen into separate columns...any help? This is greatly appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 18:20:52

Well your original post said "before a hyphen". Here's how to get both sides:

DECLARE @s varchar(50)

SET @s = 'Sports - rating'

SELECT SUBSTRING(@s, 1, CHARINDEX('-', @s) - 1) AS BeforeHyphen, SUBSTRING(@s, CHARINDEX('-', @s) + 1, DATALENGTH(@s)) AS AfterHyphen

You can change the -1 and +1 to -2 and +2 if you don't want the spaces either.

Just remove the declare and set, change the @s to your actual column, and add a FROM clause to do this on a table rather than on a variable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 18:25:05

hMMM, Here's the error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

What could this mean?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 18:34:01

Some of your data probably doesn't have hyphens. Use a CASE statement for this condition if you want to display all of the data or add a WHERE clause to only do the string functions on the data that has hyphens.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 18:44:35

Well I think there might be progress...is the nvarchar value an issue? It prints the following error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ' Lack of System – Communication - internal / external' to data type int

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 18:47:51

Wait this worked...except what if a field has two hyphens? It only removed the first

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-05 : 18:52:16

Could you post all of the requirements so that we don't go back and forth with solutions that don't meet all of the criteria? Please give an example of each of the different things that can be in your data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sarakmo
Starting Member

7 Posts

Posted - 2007-09-05 : 19:04:11

Tara, I apologize greatly...I'm new to the forum and am very appreciative of your help.

Here are all the different formats:

Column A
1. Lack of System – Talking - internal / external
--(I need to extract 'Lack of System' into its own column and 'Talking - internal/external' separate as well)

2. Lack of Comunicating – Over commitment by group c (same request as above)

3. 0 (a null, however still needs to be in result set)

4. OTHERS- (need to extract 'OTHERS')

I was using a HAVING statement since I have a group by. The query is quite long with many joins, however hopefully this helps you help me.

I'm very sorry for the earlier confusion. Many many thanks!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-06 : 00:40:46

Search for the fnParseString function in the Library Section.
There is also examples.

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-06 : 02:11:01

Hi,

DECLARE @s varchar(50)

SET @s = 'Sports - rating'

SELECT LEFT (@s,CharIndex('-', @s)-1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-06 : 03:01:56