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 |
|
companionz
Yak Posting Veteran
54 Posts |
 Posted - 2009-06-15 : 10:20:44
|
| Hi,I am trying to write a query which will protect ASCII character to insert into table.. Earlier i wrote:::Part of my code:::SET @UnitInfo = CURSOR FOR SELECT ltrim(rtrim(Acc_no)) , ltrim(rtrim(SerialNumber)) from test_table...Now i am in need to check that ASCII characters are not getting populated in the cursor..Can anyone help on how to modify this select query? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-15 : 10:24:45
|
All you need is a CHECK constraint, like thiCHECK(MyCol NOT LIKE '%[^0-9]%')for allowing only digits 0,1,2,3,4,5,6,7,8,9 into the MyCol column.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
companionz
Yak Posting Veteran
54 Posts |
Posted - 2009-06-15 : 10:39:42
|
| But the field is varchar type...And i just need to filter the special character... if it's say is appended at the trailer... |
|
|
|
companionz
Yak Posting Veteran
54 Posts |
Posted - 2009-06-16 : 11:08:37
|
| I got it done by myself finally:SET @UnitInfo = CURSOR FOR SELECT Case when ascii(Right(SerialNumber,1)) not between 33 and 126 then Rtrim(Left(SerialNumber,Datalength(SerialNumber) - 1)) else ltrim(rtrim(SerialNumber))from test_table...The above query will take care of any trailing special characters and even space.. |
|
|
|
|
|
|
|
|
ASCII Characters