Concatenate numeric and string data field in mssql

Fri, 13 Apr 2012 08:25:00 -0700

Today I've found how to concatenate numeric and string format in mssql. As we all know, concatenate two strings in mssql is very simple and well-known formula as follow


SELECT First_Name + ', ' + Last_Name FROM myTable

[More]

Replace function in SQL

Mon, 05 Mar 2012 21:43:00 -0700

Today I've found how Replace function is useful in SQL. Because I need to update all columns of every table in our database. Using Replace is very simple.


UPDATE  SET
columnText = REPLACE (columnText, 'StringToFind', 'StringToReplace')


Above query is correct for nvarchar field but when I'm trying to replace for ntext and text field, it's getting error that "replace isn't ok for text field". That's why I'm goggling and found nice example as follow.


UPDATE 
 SET columnText =
REPLACE(SUBSTRING(columnText, 1, DATALENGTH(columnText)), 'StringToFind','StringToReplace') 

Get free exam simulator from uCertify
Tue, 29 Nov 2011 20:15:00 -0700

	 

	uCertify, leading IT certification provider company is willing to spring for a license, that's a full license, for any of their test prep kits for one of my blog readers. The only requirement to get the full license is that you write a review of the software, within 30 days, that will be a guest post here on this blog.

	 

	So, I am offering this to all of my blog readers. Today is Nov 30, 2011. To get a chance to win the prep kit license, go to the ucertify site and take a look at the available tests. Decide which you would like to take.

	 

	Once you've made your choice, come back here and post a comment about which you would like and why you want to take it. Doesn't have to be anything big; just a couple of sentences is fine.

	 

	Make sure you either leave a real email address or you are signed in when you leave the comment. You don't need to leave your email address in the text of your comment if you are signed in. I can send you a message via the it toolbox messaging system in that case.

	 

	I will randomly choose the winner from the comments on Dec 10, 2011. So, you have about 10 days to post a comment.

	 

	Once I have notified the winner and they agree to review the software in a guest post here on this blog, I will give the info to Ucertify and they'll hand out the license.

	 

	The guest post doesn't need to be huge. At least a few hundred words which is just a page or two of double spaced text. As an example, this  blog post is over 400 words. I'll be glad to proofread and edit it if you would like me to mail me (pyaephyoeshein[at]gmail[dot]com).

Oracle 11g: SQL Fundamentals (1Z0-051) exam guide
Wed, 19 Oct 2011 06:51:00 -0700

	In this 2011, I feel like myself to take almost certification of what I know and what I'm using in my development space. Being programmer, I didn't say almost certificates are needed but just like non-computer science graduated like me, those certificate are very needed. Because almost people won't know why I'm surviving in IT without having computer science graduate. That's why I believe I'm not wrong to take certificate. 

	When I was in Myanmar, we use Microsoft SQL as our database because which is simple and easy to use with their UI. When I started working in Singapore (2009), our company use Oracle as database system instead of Microsoft SQL. Since that time, I'm addictive at Oracle because of the way of writing stored procedure, functions and views. That's why I think I need to have at least one Oracle certificate to let people know that I know Oracle.

	That's why I decided to take 1Z0-051 exam of Oracle. It's called SQL Fundamentals exam which is suitable for me to take as beginner state of Oracle developer. Below is the exam topic of 1Z0-051.
				 [More]

Why Coldfusion 9 cannot connect SQL 2005 Express?
Sat, 23 Apr 2011 22:16:00 -0700
In my life, mySQL database is very touch to use. Because it's very complicated to add or remove data column, rename the table name and inserting demo data without installing phpMyAdmin. That's why I decided to remove mySQL as my project database and to install SQL 2005 server Express version. 

So, download both of SQL 2005 Server and also management studio express version. Honestly, installing of SQL 2005 express version is very simple and easy. Because, my before time I've used to develop Coldfusion Project with SQL server. That's why I can say it's simple. Unfortunately, I encountered the following error when I tried to connection SQL 2005 server in Coldfusion Administrator.


				 [More]

Display the latest record with descending in Oracle
Tue, 22 Feb 2011 03:11:00 -0700
This week is very busy time for me. Because I have to implement HL7 segment with Oracle Stored Procedure. As we all know, Oracle SQL command isn't that same with Microsoft SQL. That's why some of command are very complicated for me to implement. Today, I need to retrieve the only one latest record from table with Oracle. In Coldfusion, it's very easy. To put maxrows = "1" and descending in query as follow:



	SELECT U_ID FROM TBL_USER
	ORDER BY U_ID DESC



In Microsoft, it's very simple. To put TOP command and descending in query as follow:


SELECT TOP U_ID FROM TBL_USER
ORDER BY U_ID DESC


In Oracle, it's not that easy. If we put ROWNUM and descending in query, the first record will be returned. Because ROWNUM doesn't work with Order By descending in query. That's why we need to do with query with query formula in Oracle as follow:


SELECT U_ID FROM
	(SELECT U_ID FROM TBL_USER
	ORDER BY U_ID DESC) CLONE_USER
WHERE ROWNUM = 1


Phweee...!!! Oracle crack my brain again. :)

Simple Learning about Oracle Tuning
Thu, 06 Jan 2011 17:47:00 -0700
Today I have to retrieve data from the largest table of my project in Oracle. Approximately, this table has more than 1 million records and need to retrieve data carefully when we need. Now what I need to do is to retrieve negative quantity from this table. 

There are two ways to retrieve negative quantity from the table. One is systemically and second is simple way.



	select * from nh_inv_trx_tmp
	where transaction_date < '7-Jan-2008'
	and abs(quantity) <> quantity
 




	select * from nh_inv_trx_tmp
	where transaction_date < '7-Jan-2008'
	and quantity < 0


				 [More]

Kill Query Session in Oracle
Tue, 04 Jan 2011 16:35:00 -0700
In Microsoft SQL, we can trace currently which SQL query takes execution time and load of each SQL query in SQL Profiler. In Oracle, what should we do? It's not that simple like Microsoft SQL. Open TOAD and login as SYSTEM authentication. Then type following statement into TOAD SQL.




SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE' AND USERNAME = 'MYDSN'


Run this query. After that, all active query will be displayed and note which query execution time takes so long.
				 [More]

Copy two tables in Oracle
Thu, 30 Dec 2010 16:44:00 -0700
I found that so many query statements are different between in Microsoft SQL and Oracle. To copy two table in the same database with Microsoft SQL, I normally use SELECT INTO statement as follow.




	SELECT *
	INTO  tblPerson_Backup
	FROM tblPerson




	SELECT NAME, DOB, SEX
	INTO  tblPerson_Backup
	FROM tblPerson


				 [More]

ROWNUM in Oracle and maxrows in Coldfusion
Tue, 21 Dec 2010 16:55:00 -0700
Normally, I always use maxrows attribute of CFQuery when I want to display limited records from table. Sometimes I want to display limited records from table with built-in Microsoft SQL command like TOP in Oracle. Yesterday I found that there is ROWNUM command in Oracle which can display limited records like maxrows in CF and Top in SQL. That's why I feel happy to use this command in my query. On the other hand, I want to test the performance between maxrows and rowcount. 

So I've tested as follow

With maxrows


	select NAME, DOB from nh_person



Query execution time for above statement is 31ms and debugging output is as follow

				 [More]

Difference between implicit and explicit cursors in Oracle
Tue, 21 Sep 2010 04:15:00 -0700
Today, I need to develop simple function in Oracle about data retrieving. As I mention in previous post, I'm very new to Oracle and don't know much of Oracle command and attributes. That's why I seek some information and develop simple function for it. 
In some information, I found two way to insert query data into variable as follow:

first one


DECLARE
    myvar varchar2(50);
BEGIN
    SELECT fieldone into myvar FROM tbl_one WHERE id = 1;
END;



second one


DECLARE
    myvar varchar2(50);
    CURSOR L1 IS
    SELECT fieldone FROM tbl_one WHERE id = 1;
BEGIN
    OPEN L1;
    FETCH L1 INTO myvar;
    CLOSE L1;
END;


				 [More]

Create Function in Oracle
Sat, 18 Sep 2010 19:53:00 -0700
I'm very new to Oracle because I'm start using Oracle when I reach here. When I was in Myanmar, our RDBMS is Microsoft SQL. In MsSQL, GUI is very simple and for example: if you need your ID as auto increment, set identity to true/yes in Ms.SQL properties. In Oracle, we need to create trigger for auto increment. Today, I feel like myself to create Function in Oracle for convert number format to Ordinal Format which I already created in CF UDF. Why I created the same Function in Orcale is because I want to generate report by using cfreport tag.

Here is my simple Function


CREATE OR REPLACE Function ConvertOrdinal
    --Input parameter from outside
   ( cycle IN varchar2 )
   RETURN varchar2
IS
    --Output parameter from function
    ordinalformat varchar2(5);
BEGIN
    if ((mod(cycle,10) = 1) AND (mod(cycle,100) = 1))then
         ordinalformat := 'st';
    elsif
       ((mod(cycle,10) = 2) AND (mod(cycle,100) = 2))then
         ordinalformat := 'nd';        
    elsif
       ((mod(cycle,10) = 3) AND (mod(cycle,100) = 3))then
         ordinalformat := 'rd';                     
    else
         ordinalformat := 'th';
    end if;
RETURN cycle || ' ' || ordinalformat;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;


 
Execute this function in PL/SQL


SELECT ConvertOrdinal('1') CO FROM DUAL

 
Output will display as follow

CO 
====
1 st


SQL Injection attacks by Store Procedure
Fri, 27 Aug 2010 06:32:00 -0700
I was in dilemma in these days because our website has been attacked by SQL Injection Attacks by the way of using store procedure command in URL variable. Finally, I can prevent this one cannot attack to our site by using like that anymore. The one use following store procedure command the after "?" of your website URL.
 
	DECLARE @S NVARCHAR(4000)
	SET @S=CAST(0x4400450043004C00 ... 6F007200 AS NVARCHAR(4000))
	EXEC(@S) 
	
	DECLARE @S VARCHAR(4000)
	SET @S=CAST(0x4445434C41524520 ... 736F7220 AS VARCHAR(4000))
	EXEC(@S) 

	DECLARE @S CHAR(4000)
	SET @S=CAST(0x4445434C41524520 ... 736F7220 AS CHAR(4000))
	EXEC(@S)


The above three variants have been injected through an HTTP GET: 
Decoding the binary string to its textual form reveals the T-SQL script below, which has been slightly formatted and edited for purposes of clarity. For those who are not proficient in the syntax, the script simply creates a cursor through which it browses for all columns of certain data types (textual) in all user-defined tables underlying the database. Next, the T-SQL script affixes a JavaScript reference (to the malicious script) to the current values contained in each such column.
				 [More]

Electronic Press Scrutiny & Registeration System
Fri, 27 Aug 2010 06:32:00 -0700


This website called "Electronic Press Scrutiny & Registeration System" is just for Myanmar Press Scrutiny & Registeration System. It's just offline program. This program supports our clients to send their publications step by step through this program and comment to these.

Systems :

IIS, CFMX, SQL 2005

Google App Engine data manipulation with Coldfusion
Wed, 02 Dec 2009 06:32:00 -0700
Today, I've recently developed one simple project with Coldfusion in Google App Engine. To manipulate data in Google App Engine is complicated because we can use only GQL (Google Query Language) instead of SQL. Likewise, displaying data in Google App Engine with OpenBD is quite different with Adobe Coldfusion. That's why, it's too hard to handle to everything. Ok, I'll show how to create Table in Google App Engine and retrieve data either.

Create Table and Insert Data



	car = StructNew();     
	car["Class"] = "Sedan-Midsize";     
	car["Make"]  = "Audi";     
	car["Model"] = "A6";     
	keyString = googleWrite(car, "MyCarLot"); 




Ref : http://wiki.openbluedragon.org/wiki/index.php/GoogleAppEngine:Datastore

Display data


     
	select from MyCarLot 
 
	
     
	 #getAllTags[i].Class#

Against All Odds - MsSql

Concatenate numeric and string data field in mssql

Replace function in SQL

Get free exam simulator from uCertify

Oracle 11g: SQL Fundamentals (1Z0-051) exam guide

Why Coldfusion 9 cannot connect SQL 2005 Express?

Display the latest record with descending in Oracle

Simple Learning about Oracle Tuning

Kill Query Session in Oracle

Copy two tables in Oracle

ROWNUM in Oracle and maxrows in Coldfusion

Difference between implicit and explicit cursors in Oracle

Create Function in Oracle

SQL Injection attacks by Store Procedure

Electronic Press Scrutiny & Registeration System

Google App Engine data manipulation with Coldfusion