Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 10th, 2005, 03:56 PM
Authorized User
Join Date: Apr 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default syntax to find a string in a string

Is there a sql syntax or function that will allow me to parse the length of a string out of a string?
i.e. fieldname animals: dogs_cats, horse_cats
I need to parse to the '_'
len(instr(animals, '_'))

Or do I need to perform a do while or loop of somekind?


Old October 10th, 2005, 04:27 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

The CHARINDEX function will return the starting position of a substring within a string.

The SUBSTRING function will return a substring starting at a given position for a given length.

You can combine these, as for example, to return a substring of your first animal delimited by a "_":
SUBSTRING(animals, 1, CHARINDEX(animals, '_') -1)
TSQL is NOT the place you should be doing character/string manipulation, though ...

An even more important question is why you have composite data like this in the first place. This violates first normal form, and will cause you untold grief going forward. You'll find it very messy to query against such data, and it's ridiculously difficult to do things like accurately count how many rows have 'cats' in their animals column, etc.

Jeff Mason
Custom Apps, Inc.
Old October 10th, 2005, 06:06 PM
Authorized User
Join Date: Apr 2005
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts

Jeff, thanks for your reply. Unfortunately, i wasn't part of the design on this db and would never have recommended building it this way. My only alternative is to fix the problem - which I wasn't contracted to do or make it work somehow which I should be able to do and let them keep calling me back to resolve more of their data issues.

Thanks again for your concern & help on this.
I made your syntax work and I'm getting the results I need.

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to find a string in another string in vb6 satish_k VB How-To 3 March 30th, 2007 12:17 PM
Find String in query vishwadh SQL Server ASP 3 September 6th, 2006 03:16 AM
Syntax help with string-length and XPath EstherMStrom XSLT 1 February 9th, 2005 08:02 PM
JSP string find characters ndanger J2EE 2 November 24th, 2004 09:52 AM
select string and error syntax fdmfdm Access VBA 3 November 26th, 2003 09:04 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.