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 |
|
nhaas
Yak Posting Veteran
90 Posts |
 Posted - 2008-06-03 : 12:36:11
|
| Hi allI have just a simple search, but I want to drop everthing after the "/". I am serching for 2500 but some of the 2500 looks like "2500/3". Basically I want to drop everthing including the slash "/" treating the 2500 and the 2500/3 as the same.thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-03 : 12:54:37
|
| Use CHARINDEX to find the / and then use SUBSTRING or LEFT functions to get everything before it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 13:14:34
|
| And make sure you check the presence of '/' before applying the SUBSTRING/LEFT functions else code will break. |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2008-06-03 : 16:36:38
|
| OK a little confused I did "left(t2.SET_TYPE,charindex('/',t2.SET_TYPE))" and I still see the / and everything after it. I would like to see only 2500, and not 2500 and 2500/3.I think that I am still missing something, thanks for the help.whole code:SELECT SETTYPE, COUNT(*) as record_count FROM TESIS2 t1 WHERE NOT EXISTS (SELECT * FROM UNITS t2 WHERE t1.SETTYPE = left(t2.SET_TYPE,charindex('/',t2.SET_TYPE)) ) and SETTYPE NOT LIKE '*%' GROUP BY SETTYPE order by settype |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 00:14:29
|
quote:
Originally posted by nhaas
OK a little confused I did "left(t2.SET_TYPE,charindex('/',t2.SET_TYPE))" and I still see the / and everything after it. I would like to see only 2500, and not 2500 and 2500/3.I think that I am still missing something, thanks for the help.whole code:SELECT SETTYPE, COUNT(*) as record_count FROM TESIS2 t1 WHERE NOT EXISTS (SELECT * FROM UNITS t2 WHERE t1.SETTYPE = left(t2.SET_TYPE,CASE WHEN charindex('/',t2.SET_TYPE)>0 THENcharindex('/',t2.SET_TYPE)-1 ELSE LEN(t2.SET_TYPE)END) ) and SETTYPE NOT LIKE '*%' GROUP BY SETTYPE order by settype
Modify like this and try |
|
|
|
nhaas
Yak Posting Veteran
90 Posts |
Posted - 2008-06-04 : 17:47:18
|
| Thank you |
|
|
|
|
|
|
|
|
Search help