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 |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2009-02-10 : 10:45:55
|
User input is a TextBox which indicates company. User will key in example company = 32. In table, the company field have none or one or more leading 0 (zeros). Company field is nvarchar(20). In the where clause I would like to trim any leading zeros off the table field before comparing value. Not sure how to do this.CREATE PROCEDURE dbo.sp_LaborExceptionInquiry( @Company nvarchar(20),)WHERE dbo.Region.CompanyRegionId = @Company ......  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-10 : 10:48:23
|
| [code]CREATE PROCEDURE dbo.sp_LaborExceptionInquiry( @Company nvarchar(20),)WHERE dbo.Region.CompanyRegionId*1 = ltrim(rtrim(@Company))*1 .....[/code] |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-02-10 : 10:49:17
|
| Won't be quick butWHERE right(rtrim(Region.CompanyRegionId), len(Region.CompanyRegionId)-patindex('%[^0]%',Region.CompanyRegionId)+1) = @Company==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-02-10 : 10:51:17
|
| Better wouild be to pad the string you are searching with as that could use an index.If it's fixed length (say 20)WHERE dbo.Region.CompanyRegionId = right((replicate('0',20) + @Company, 20)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2009-02-10 : 11:47:26
|
| Both worked. Thank you guys. |
|
|
|
|
|
|
[Resolved] Using TRIM in Where clause