Posts Tagged ‘alternative’

-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null

--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'

/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL


Read Full Post »

-- The objective is to find the second occurence of space and insert a carriage return. 

-- First Method:

DECLARE @passed VARCHAR(max)
SET @passed = 'First Second Third Fourth'

-- Extract all text until the second occurence 
select substring(@passed, 1,
		charindex(' ', @passed, charindex(' ', @passed) + 1)-1
		) [Space Occured First]

union  all 
select CHAR(13) + CHAR(10)

-- Extract all text after the second occurence 
select substring(@passed
			, charindex(' ', @passed, charindex(' ', @passed) + 1)
			, len(@passed)
		) [Space Occured Second]
-- Second Method:

declare @tbl_Foo table 
	FiledToChange varchar(50)

insert into @tbl_Foo values -- inserting this way can only be from SQL server 2008 and above 
('a b c')
,('1 2 3 4')

select FiledToChange as [Before] from @tbl_Foo

update @tbl_Foo
	set FiledToChange = ISNULL(
		case when charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1 > 0 then -- Look for a second space 
			substring(FiledToChange, 1, -- This will get two words 
				charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1)
			CHAR(13) + CHAR(10) -- add the carriage return and line feed 
			substring(FiledToChange -- add the rest of the text 
				, charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)
				, len(FiledToChange))
select FiledToChange as [After] from @tbl_Foo
-- Since SQL does not support displaying multiple lines, you will notice that instead there is an extra space. Applications supporting multiple lines can read this and conver it to multiple lines

Read Full Post »

 Cross Join: 
 Each row in the first table is paired with all the rows in the second table. 
if OBJECT_ID('tempdb..#a') is not null 
	drop table #a 

if OBJECT_ID('tempdb..#b') is not null 
	drop table #b 

create table #a 
	acol varchar(10) 
insert into #a values 

create table #b 
	bcol varchar(10) 
insert into #b values 

select * from #a 
select * from #b 

select * from #a cross join #b 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))


--For Function:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))

create FUNCTION [dbo].[myUDF]
	@var AS VARCHAR(100)

Read Full Post »

Assume the below scenario:

A system checks a stock level for a grocery store that sells 3 items and whenever a certain item runs out it flags up a message.

Say the 3 items are:

To make use of binary masking (also known as bitwise) to be able to track which needs to be highlighted, we need to do the following:

1) We will assign a binary number to each one of the item, 1 for denoting we need to flag a message and 0 f0r doing nothing. Each of the the items in turn will have a fixed position. So in this instance there are three position for three items and we can assign it as below:

Item Position Binary Value
—– ——— ————-
Mango 0 1
Orange 1 0
Apple 2 1

2) Now, we need a single decimal number that represents which items jave a binary 1 to flag a message and which don’t. To do that we use the Position value and treat that as the power of 2.
So, we use Position 1 and 3 (because those have the binary value 1 binded) and it should equate to:

(2^0) = 1
+ (2^2) = 4
Total = 5

3) The value 5 can then be sent to the section of the system which flags messages. It should then have a script to reverse it back to a binary number which is 101.

The position number is fixed and therefore can be easily be deduced as to which binary number is binded to whcih item.

This way by only sending an integer number we can for a group of item attached and interchagne flag data.

>Returns the binary representation of a number:

-- courtesy to a script somewhere in the internet
CREATE FUNCTION [csrt].[udf_ReturnBinaryPattern] 
	@Byte INT


    DECLARE  @Pattern CHAR(8) 
    SET @Pattern = '' 

    SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) + 
    convert(VARCHAR,(@Byte & 2) / 2) + 
    convert(VARCHAR,(@Byte & 4) / 4) + 
    convert(VARCHAR,(@Byte & 8) / 8) + 
    convert(VARCHAR,(@Byte & 16) / 16) + 
    convert(VARCHAR,(@Byte & 32) / 32) + 
    convert(VARCHAR,(@Byte & 64) / 64) + 
    convert(VARCHAR,(@Byte & 128) / 128) 

    RETURN (@Pattern) 


>Optional – Explaning & bitwise AND operator:
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.

SELECT 170 & 75

Result is 10 (thisis a decimal number)

The & operator tranforms number or charters into binary and then adds them up:

170 = 0000 0000 1010 1010
75= 0000 0000 0100 1011
10 = 0000 0000 0000 1010

Another artice: http://sqlfool.com/2009/02/bitwise-operations/

Read Full Post »

-- Unpivot statement 

select * from @FruitSales

FruitName		SellingPrice	CostPrice
----------  		--------		-----------
Mango			296.23			26.09
Apple			259.45			19.04

Select *
from (select * from @FruitSales) original 
	CostFigures for 
	PriceType in (SellingPrice, CostPrice)
) as unpvt

FruitName		CostFigures		PriceType
-----------		------			----------
Mango			296.23			SellingPrice
Mango			26.09			CostPrice
Apple			259.45			SellingPrice
Apple			19.04			CostPrice

Read Full Post »

-- There two ways of using Temp tables and carry out any "Data Manipulation Language (DML)" operations. 

IF OBJECT_ID('tempdb..#a') is not null 
	drop table #a

create table #a
	Magic varchar(10) 
insert into #a values ('Change Me') 

select * from #a 

------------- Start of Option 1 (using Insert into Exec) ------------------- 
declare @MySQL varchar(100) 
set @MySQL = 'select case when Magic = ''Change Me'' then ''Option 1'' end 
from #a ' 

insert into #a exec(@MySQL)
delete from #a where Magic = 'Change Me' 
select * from #a
------------- End of Option1 ------------------- 

----------- Start of Option 2 ------------------- 
declare @sql varchar(1000) =
'update #a
set Magic = ''Option 2'';'

exec dbo.executeSQL @sql -- This SP just executes the string and nothing else.  The string needs to be executed in another SP

select * from #a

-- >>  Defintion of dbo.executeSQL: 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.executeSQL') AND type in (N'P', N'PC'))

	@sql varchar(max)
   exec (@sql) 

----------- End of Option 2 ------------------- 

Read Full Post »

Older Posts »