Parsing strings in T-sql

Question

Post reply

Parsing strings in T-sql

SQLEngine

SSCommitted

Points: 1633
More actions
August 27, 2010 at 2:27 pm

#225328

Hi all,
I have a table full of server names some of which include "."
How can I strip out everything to the right of "." including it, and select just evrything to the left.
When I use this syntax I get an error:
select substring (ServerName, 1, (charindex('.',ServerName)-1))
FROM Server
Error:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
select SUBSTRING(ServerName, 1, NULLIF(CHARINDEX('.', ServerName) - 1, -1)) from Server
I get a bunch of NUULL values for the ones that don't have "." in it. I want to get the ServerName even for the ones that don't have "."

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

Add a

WHERE CHARINDEX(...) > 0

Some of your rows don't have the '.', so the substring throws an error.

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

try this, it could be cleaned up and made a little simpler but this just adds an isnull to the version you posted. 😉

select isnull(SUBSTRING(ServerName, 1, NULLIF(CHARINDEX('.', ServerName) - 1, -1)), ServerName from Server

_______________________________________________________________

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/

SQLEngine SSCommitted Points: 1633 More actions · Answer 3

Thanks

Isnull works, I like that.

Can't say that I understand WHERE CHARINDEX(...) > 0 syntax

WayneS SSC Guru Points: 95460 More actions · Answer 4

SQLEngine (8/29/2010)
Thanks
Isnull works, I like that.
Can't say that I understand WHERE CHARINDEX(...) > 0 syntax

This filters the result set to just those records that do have a period.

If you didn't do this, then if you had a record without a period, the the position of a period in the string is 0, and you get the error that you're trying to avoid.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

How about this:

SELECT LEFT(ServerName, CHARINDEX('.', ServerName + '.') - 1) FROM Server

SQLEngine SSCommitted Points: 1633 More actions · Answer 6

🙂 That should work too, you're addidng a '.' manually so that there's no error in case it's not present. very nice