| Author |
Topic |
|
NguyenL71
Posting Yak Master
228 Posts |
 Posted - 2009-01-14 : 18:52:38
|
| I keep getting the error messages and cannot figure out why or someone has a better way to rewrite this query.Thank you in advance.Error:.Net SqlClient Data Provider: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.IF OBJECT_ID('Tempdb.dbo.##Staging', 'u') IS NOT NULL DROP TABLE ##StagingGOCREATE TABLE ##Staging( Idx INT NULL, Addr3 CHAR(20) NULL)goinsert ##Staging values (18614, 'SN BERNRDNO CA')insert ##Staging values (33031, 'VICTORVILLE CA')insert ##Staging values (18687, 'SAN DIEGO CA')insert ##Staging values (96681, 'SAN DIEGO CA')insert ##Staging values (68994, 'CARMICHAEL CA')insert ##Staging values (163692,'SN BERNRDNO CA')insert ##Staging values (178462, 'ESCONDIDO CA')insert ##Staging values (189431, 'CHULA VISTA, CA')insert ##Staging values (6846, 'BANNING , CA')insert ##Staging values (11318, 'EL CAJON ,CA ')go SELECT * FROM ##Staging Go-- Testing, not working... SELECT idx, Addr3, SUBSTRING(Addr3, 1, (LEN(Addr3)- 2) ) FROM ##Staging--ORDER BY idx ASCgo-- Rule: return only city name.-- Desire result.Idx Addr3----------- --------------------18614 SN BERNRDNO33031 VICTORVILLE18687 SAN DIEGO96681 SAN DIEGO68994 CARMICHAEL163692 SN BERNRDNO178462 ESCONDIDO189431 CHULA VISTA6846 BANNING11318 EL CAJON |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NguyenL71
Posting Yak Master
228 Posts |
|
|
NguyenL71
Posting Yak Master
228 Posts |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-01-14 : 19:08:13
|
| Very ugly but it works:SELECT idx, Addr3, rtrim(ltrim(substring(rtrim(ltrim(replace(Addr3, ',',''))),1,(len(rtrim(ltrim(replace(Addr3, ',',''))))-2))))FROM ##Staging |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
NguyenL71
Posting Yak Master
228 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Need help on the query.