Let’s say, I need a random number within a specific range, how do I do that?
Here’s the code that will do it:
1> DECLARE @i_min int
2> DECLARE @i_max int
3> SELECT @i_min = 0
4> SELECT @i_max = 10
5> SELECT MY_RANDOM_NUMBER = ROUND(((@i_max - @i_min) * RAND() + @i_min), 0)
6> go
MY_RANDOM_NUMBER
--------------------
7.000000
Today, I’ve been checking if there are any videos on YouTube on Sybase ASE’s dbcc traceon usage and I’ve found this interesting video by Lester Leonares about a nice functionality to log user activity to a file:
(This video is a part of the SAP D&T Academy, definitely worth checking out.)
Googling further on the “set tracefile”, I’ve found a White Paper by Sybase entitled “Application Tracing in Adaptive Server Enterprise (ASE) 15” (opens pdf file in new window).
Below are some notes from the video and the white paper.
To enable tracing:
set tracefile "<file-path>" [for <spid>]
To disable tracing:
set tracefile off [for <spid>]
The “set” commands in ASE whose output can be traced under application tracing context are:
set show_sqltext <on/off> set showplan <on/off> set statistics io <on/off> set statistics time <on/off> set statistics plancost <on/off> -- and all ‘set option’ subcommands
Obviously, as you could imagine, there are some documents in Sybase Infocenter about Finding Slow Running Queries where you can read about tracefile and see some examples. This link is an excerpt from a Sybase book on Performance and Tuning Series: Query Processing and Abstract Plans (opens a 3.9MB pdf file in new window).
This is how I do it:
UPDATE <tablename>
SET <column> = SUBSTRING(<column>, PATINDEX('%[^0]%', <column>), CHAR_LENGTH(<column>))
WHERE <column> LIKE '0%'
Obviously, the most interesting part for me is the:
'%[^0]%'
construct that finds the position of the first non-ZERO character in the <column> to start substring from.
For detailed info about used functions, please visit Sybase’s documentation:
– patindex