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.
| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2004-02-25 : 18:56:17
|
| I have a column in a table that is an nvarchar. All of the data in the column is listed as something similar to this.XX-ABC-SXX-ABC-MXX-ABC-LXX-ABC-XLXX-ABC-XXLThis works fine for the individual items, but now we need to get a count of items regardless of size. If the sizes were all the same I could just trim off the last n characters, but they can range anywhere from 1 to 3 characters long. My next thought was to split up the field in the query at the dashes, and then reassemble the first two sections. Not sure how I would go about doing it.Thanks for any help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-25 : 19:00:28
|
| Is this what you are looking for:SELECT COUNT(*)FROM Table1GROUP BY SUBSTRING(Column1, 1, 6)?If not, please explain further. What would your result set be given the sample data that you posted?Tara |
 |
|
|
dsdeming
479 Posts |
Posted - 2004-02-26 : 13:32:53
|
| If the requirement is just to trim off the last hyphen and everything after it, and there's no guarantee that the number of leading characters will not change, you can do something like this:SET NOCOUNT ONCREATE TABLE #Test ( Size varchar( 20 ))INSERT INTO #Test SELECT 'XX-ABC-S'INSERT INTO #Test SELECT 'XXX-ABC-M'INSERT INTO #Test SELECT 'XX-ABC-L'INSERT INTO #Test SELECT 'ZZZZZ-ABC-XL'INSERT INTO #Test SELECT 'XX-ABC-XXL'SELECT REVERSE( SUBSTRING( REVERSE( Size ), CHARINDEX( '-', REVERSE( Size )) + 1, 20 ))FROM #TestDROP TABLE #TestHTHDennis |
|
|
|
|
|
|
Group By: Part of a nvarchar