Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Take Integer Of A Number?


for example which function can i use to convert the real number into the corresponding integers?

4.5 ->5
3.4->3
18.9->19
Thans


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
'nother Integer Question For A Number Of You
"number of you"??? Integer???? get it??? OK...it's early/late/whenever...

without the search function, I am unable to easily find what I am nearly sure is an easy one...

but...I am converting a CSV string to a table of integers using a stolen fn_CsvToInt function that returns a table of integers...found THAT on here earlier, b'gawd ;)

In any case, I am using IF (ISNUMERIC(yada) = 1) to validate that the sub-string of the input CSV list of number is, in fact, a number...but what I am after is INTEGERS only...so if someone sends in some stupid stuff (though I am SURE none of MY users would do such a thing ;) ) like:select * from fn_CsvToInt('1,43,5.7,byte_me,100)

what I want to return is 1, 43, 100 (ignoring the 5.7 and 'byte_me' entries in the list). However, using the ISNUMERIC allows the sneaky '5.7' entry past, and then I get an error trying to convert it to an integer later.

Any easy way to check for integers? Or do I need to whip out a quick fn_IsInteger UDF for my evil and exclusionary purposes?

Thanks!
Paul

View Replies !   View Related
Cut Decimals And Return The Integer Of A Number
Hi, I was wondering how I can have the integer og a number that haves decimals.
I tried with FLOOR and ROUND function but it didn't work.
Does anyone knows how to do this?
Thanks

Beli


View Replies !   View Related
Is It Possible To Get The Month Name With Only An Integer Representation Of The Number
Hi,
Is it possible to get the month name with only an integer representation of the number.

i.e January ,February..... the DATENAME only takes a date as a value.

thanks in advance


View Replies !   View Related
Split A Decimal Number Into The Integer Part And The Fraction Part
I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View Replies !   View Related
How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time
Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View Replies !   View Related
Limitations In Term Of Number Of Tasks And Number Of Columns
Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View Replies !   View Related
How To Change A Decimal Number To Percent Format Number?
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View Replies !   View Related
Integer / Integer
Hi All,

If I run "SELECT 610/100", query analyser returns 6! (wrong)

If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)

I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?

Is there some setting on my server which says Integer / Integer = Integer ?

Regards

Xo

View Replies !   View Related
UniqueID Vs Integer
My question is, i guess, a simple one:
When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?
 

View Replies !   View Related
GUID Vs Integer
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)

View Replies !   View Related
Getting Value From Database Into Integer
I have an sql statmetn that counts all the votes in the table. i need this to calculate the quota. My problem is how to i get the value(i.e the count of the votes) into the area thats colored red below?
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);
Response.Write(quota);

View Replies !   View Related
Inserting An Integer
Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet
Dim ds As New DataSet
Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn)
cn.Open()
da.Fill(ds)
cn.Close()
Return ds
End Function
Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.

View Replies !   View Related
Help With Integer Field
hi all,

i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.

View Replies !   View Related
How Do I Format An Integer
How do I format an integer. Add commas.1234565 1,234,565TIA

View Replies !   View Related
Default Value For Integer
I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.

View Replies !   View Related
Format Integer
I have some integers I want to format prefixed with zeros, e.g. 1235 would become 001235 and 445 would become 000445,

View Replies !   View Related
Integer, Varchar And IN
In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname
from category
where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."

Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?

(same question is also posted at sql-server-performance.com forums)

View Replies !   View Related
Integer Datatype
Hi all,

I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer


Thanks in advance.

Sanjeev Kumar

View Replies !   View Related
Even Integer Function
Dear Fellow SQL Server 7 Administrators,

I was wondering if there is a way to call back in a SQL Query Analyzer all even or odd integers in a user table? By this I mean, 1,3,5,7,9,11,13,15,17, etc. If you have any ideas, I would be greatly appreciative. I am currently trying to construct a function statement but if someone has a better plan or one already drawn up, let me know.

Thanks,
Daimon

View Replies !   View Related
Integer To Datetime UDF
Trying to write the most effective UDF to convert INT to Datetime.
We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.


ALTER FUNCTION IntegerToDatetime (@int INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @IntegerToDatetime int
DECLARE @time DATETIME
SET @time = '2001-01-01'
SET @IntegerToDatetime =
CASE
WHEN LEN(@int) = 7
THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int)
WHEN LEN(@int) = 6
THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int)
WHEN LEN(@int) = 5
THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int)
+ '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int)
WHEN LEN(@int) = 4 THEN cast(@time AS INT)

END
RETURN (@IntegerToDatetime )
END

GO


INPUT
-------------------------------
990831
981019

RESULT
-------------------------------
1900-01-02 00:00:00.000
1900-05-27 00:00:00.000




http://www.sqlserverstudy.com

View Replies !   View Related
Integer Question
Hi there!

I am building an application that counnts clicks on an ad. I use the integer field to count them but someone told me there is a limit number this field can count - is that correct? if so, is there a solution for my problem?
Thanks and happy 2007!
R

View Replies !   View Related
Add A Fixed Value (integer)
philippe writes "Hello ,
i am just starting with sql...

I have a table: Table_user

Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first

I will appreciate some tips...."

View Replies !   View Related
Integer To Datetime
Hi

I use this to get all users from active directory.


SELECT *

FROM openquery(

adsi

,'SELECT name, AccountExpires FROM

''LDAP://company/OU=users,dc=company,dc=com''')

WHERE AccountExpires IS NOT NULL AND AccountExpires not in('0','0x7FFFFFFFFFFFFFFF','9223372036854775807','')

AccountExpires returns values like 128514708000000000 (This value represents the number of 100 nanosecond intervals since January 1, 1601 )

How do I convert this value to Datetime?


select getdate() returns avalue like 2008-04-18 10:00:00.00 and that's how I'd like my AccountExpires

View Replies !   View Related
How To Set Integer Value In Buffer
I am trying to set a decimal value to the pipelinecolumn buffer, but it doesnt get set, and the value is NULL.

Here is the portion of the code of what I am trying to do:

if (columnInfos[x].colName.EndsWith("_CRC"))

{

int a;

a_cmp tst = new a_cmp();

a= tst.a_crc32(inputbufferstream); this function returns a integer value



buffer.SetDecimal(colInfo.bufferColumnIndex, Convert.ToDecimal(a));

}

Please let me know how to set a decimal value in the buffer.

 

View Replies !   View Related
Unique Integer IDs
Hello,

I wanted to hear from you - if you've used any unique integer generation technique in the context of a disconnected smart client/local data store and have been successful with it.

Any ideas/suggestions will be great.

Thanks,

Avinash



View Replies !   View Related
Datetime To Integer
I wasjust toldthat it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.

View Replies !   View Related
Insert Integer Null Value
 Hi,I have SQL Server DB, and I tried to insert some data. I used StorProc to do the insertion as follows:ALTER PROCEDURE dbo.InsertPage        (    @PageID int,    @ParID int,    @ChiID int,    @PageContent ntext    )AS        INSERT INTO MP_Page                            (PageID, ParID, ChiID, PageContent)      VALUES     (@PageID,@ParID,@ChiID,@PageContent)    RETURNThe problem happened when I tired to insert int null value in field ChiID which is allow null and here is my codeprivate void InsertPage(PagesDB PageDB, out pagedetails pages)        {            if (txtChild.Text == "")            {                int varChildID;                varChildID = int.Parse(txtChild.Text.Trim());                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), varChildID,                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }            else            {                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), int.Parse(txtChild.Text.Trim()),                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }                PageDB.InsertPage(pages);        } the error message says Input string was not in a correct format.Any idea ??Thank you  

View Replies !   View Related
Size Of Integer In SQL Database
Hi All,
What is the size of  "Integer" in SQL database.
Thanking you
Abdul 
 

View Replies !   View Related
Stored Proc Integer
I have this block of code that is supposed to go out to a SQL2k5 Express DB, get the number value from a record based on a request.querystring containing the table ID number.I get the request.querystring and store it in a variable. By nature this is stored as a String data type.Then I try to convert the string into an integer using both the "Convert.ToInt32(IDNum)" and the "Int32.TryParse(Request.QueryString("ID"), IDNum)" methods.The IDNum variable is to be used to inject into a stored proc as shown below. conn = New SqlConnection(ConfigurationManager.ConnectionStrings("database_connection").ConnectionString)conn.Open()cmd = New SqlCommand("GetMyNumber", conn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.Add("@MyNumber", Data.SqlDbType.Int).Value = IDNumSession("EndResult") = cmd.ExecuteScalarcmd.Dispose()conn.Dispose()As you can see the result is to be stored into a session variable to be used later.My query works just fine when created as an actual query and I supply the numerical value of the record. So I know that's working. I know it is connecting to the db based on the debugging I've done. I just, for some reason, cannot get that data type to convert. So my questions are:1. How do I convert the queried ID number to an integer so that I can use it in my stored proc?2. In the end, can the result be stored in the session variable as I have shown or are the session variables only for string types? 

View Replies !   View Related
Can't Convert Record To Integer
Hi All
I'm getting an error that says that this can't be converted to an integer.
Here is the line that gets the error. dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()
I have also tried.  dt = (DataView)(EventDataSource1.Select(dssa);
I am programming in VB
here is teh rest of my code.
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()

View Replies !   View Related
How Do I Add Time With Integer Value In MSSQL ??
Hi, Expert
 
How Do I Add Time with integer value in MSSQL ??
For Example: 07:00:50 + 20 = 07:01:10
Note: 20 is in second
 Thanks in advance

View Replies !   View Related
Convert Returned Value To Integer
I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.-----------------------------------------------------DECLARE @TransactionID int    <------ INSERT statement (after inserting the record, select the identity ID) --------->        Select @TransactionID = @@Identity    RETURN------------------------------------------------------...Dim transactionID As Integerconnection.Open()Dim reader As SqlDataReaderreader = command.ExecuteReader(CommandBehavior.CloseConnection)Dim p3 As New SqlParameter("@TransactionID", SqlDbType.Int)p3.Direction = ParameterDirection.OutputtransactionID = Convert.ToInt16(p3.Value)connection.Close()...I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

View Replies !   View Related
Can Someone Explain The Precision Of An Integer In A Sql Db Pls
Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???

Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.

Thanks

View Replies !   View Related
Converting Integer To Datetime
Hi All,How do you convert int value to datetime datatype in sql servere.g 900mins to hh:mm:ssRegardsOla*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Integer Datatype Question
I have a column setup using the Int datatype and a length of 4. First: does this mean that the allowable range is from -9999 to 9999? Second: I can't seem to change the 4 to anything else, how can I modify the length :confused:

View Replies !   View Related
Add Leading Zeros To Integer?
I need to cast an integer to a string to append to another string for a barcode.

How can I get 1 -> '0001' OR 100 -> '0100'

Any suggestions?

Mike B

View Replies !   View Related
Overlapping Integer Ranges ??
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...

I have a table:

Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400


Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.

I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.

Row 1: 30--------------------240
Row 2: 40--------------120
Row 3: 50----------------------260
Row 4: ...1300---------1440


I would like to include starttime-endtime ranges that do not overlap with any other integer range.

which in this case would be:

Rownumber starttime endtime
1 l 30 l 260
2 l 1330 l 1400

I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?

Thank you for the help!

View Replies !   View Related
Bit Field Vs. Integer With Nulls
If I have a field where 99.9% of the time the answer is going to be "No", would I be better, in terms of disk space, using:

A bit field

OR

A tiny int field, with a NULL for the 99.9% that are "NO", and a 1 for those that are "YES".

I'm using SQL Server 7.0.

My application developer has no preference.

Thanks.

View Replies !   View Related
Gaps In Integer Ranges
hello, i have quite a challenge on my hands here and would appreciate any help. :confused:

I have a table variable that stores integer ranges representing times of the day:

select * from @reservations

room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000


I need to be able to return the minutes that are open for each room. The @reservations table shows me the times that are blocked.

I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.

Based on the @reservations table above, I'd like to write something that returns:

room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440

This result represents the times in minutes that are available.

I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.

I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)

View Replies !   View Related
Cast As Integer Problem.
I have DB that contains a field named GarmentSize. The field type is TEXT as it can contain for example

8
10
12
14

or

L
XL
XXL

The problem is when I run a query and order by GarmentSize the results displayed are as follows if it contains numbers

10
12
14
8

Instead of

8
10
12
14

If I use order by CAST(GarmentSize as Integer) this works fine, unless the field contains text then is throws the following error.

Syntax error converting the varchar value 'XL' to a column of data type int

Is there a way of determining if the field contains characters that can be casted before doing the CAST?

View Replies !   View Related
How Can I Convert Varchar Value To Integer
hi i want to insert the varchar value inter column, how can i convert the values and insert in to the record if i give direct column name i am getting a message
insert into EMP(id)
SELECT name from STU

i am getting a message like this
Syntax error converting the varchar value '200.00' to a column of data type int.

View Replies !   View Related
How To Convert A Hex String To An Integer?
Hi folks,
I'm trying to import data from a text file (UnicodeData.txt) into
an SQL table. Some of the fields are unicode values (16 bits)
expressed as four hexidecimal digits. I've succeeded in importing
the data as character strings, but I have not found a way to convert
them into numbers. (They could be stored as int or nchar.)
I've tried convert(binary/int/whatever, string); E.g.
select convert(int, '0x1111') from import_unicode
gives the error
Syntax error converting the varchar value '0x1111' to a column of data type int.

I could write code to strip off one character at a time, convert the
hex digit to a decimal value, shift left, etc., but I find it hard
to believe that's the best way.

Any help is appreciated. Please email answer to lars_huttar@sil.org
as I don't read this board.

Thanks,
Lars Huttar
lars_huttar@sil.org

View Replies !   View Related
Convert Integer Into Datetime Value In DTS
I need to convert an integer value into a datetime. I've tried using CONVERT and CAST, but they return an arithmetic overflow error.

The integer value is the number of seconds since 1 Jan 1970 00:00.
eg: 977189260 = 19 Dec 2000 1:27pm

I would like to implement the conversion as part of a DTS package. Any ideas at all would be extremely helpful.

Thanks
Phill

View Replies !   View Related
Save Character And Integer
hello,
i'm trying to save both character and integer into a field.
but i cant seem to be able to do so.
which data type am i supposed to select?

View Replies !   View Related
In Symmetric Key Integer Value Will Encrypt
Hi
I am using sql server 2005, I want to encrypt data and i am using Symmetric key.
In Symmetric key encrypt the varchar , varbinary data encrypt but for integer it show the error.
so please suggest me can we encrypt the integer data.
if yes then how ?
In below example if we use integer then encrypt it will show error.




--1) Create master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asit'

--2) Create certificate.

CREATE CERTIFICATE CertificateTest2

WITH SUBJECT ='CertificateTest2'

--3) Create SYMMETRIC KEY .

CREATE SYMMETRIC KEY TestSymKey

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE CertificateTest2

--4) Open SYMMETRIC KEY .

OPEN SYMMETRIC KEY TestSymKey

DECRYPTION BY CERTIFICATE CertificateTest2;

--5) create table.

create table SYMMETRIC1

(

id varchar(100) not null primary key ,

name varbinary(MAX) not null,

card_num varbinary(MAX) not null

);

--6)SEA THE RECORDS

SELECT * FROM SYMMETRIC1

--7)INSERT RECORD IN TABLE

INSERT INTO SYMMETRIC1(ID,NAME,CARD_NUM)VALUES (

EncryptByKey( key_guid('TestSymKey'),'2',1,'SQL Server') ,

EncryptByKey( key_guid('TestSymKey'),'Asit sinha',1,'SQL Server'),

EncryptByKey( key_guid('TestSymKey'),'11',1,'SQL Server') )

--8)FETCH ENCRYPTED RECORD FROM TABLE AND DECRYPT THE VALUE

SELECT

convert(varchar(max),DecryptByKey(id,1,'SQL Server')) as ID,

convert(varchar(max),DecryptByKey(Name,1,'SQL Server')) as Name,

convert(varchar(max),DecryptByKey(card_num,1,'SQL Server')) as Card_Num

FROM SYMMETRIC1

--9)SEA THE RECORDS

SELECT * FROM SYMMETRIC1

Asit Sinha

View Replies !   View Related
Concatenate Integer And Varchar
I have a table like this:

tbl_Projects
-------------------------
ProjID | Description
-------------------------
1 | First
2 | Second
3 | Third

I want to write a SELECT statement that will output in this format:
1 - First
2 - Second
etc...

I've tried these two statements:

SELECT (ProjID + ' - ' + Description) AS Projects
FROM tbl_Projects
//Error: Conversion failed when converting the varchar value 'First' to data type int.

and

SELECT CONCAT(ProjID, CONCAT(' - ', Description)) AS Projects
FROM tbl_Projects
//Error: The data types int and varchar are incompatible in the concat operator.

Is there any way to concatenate an int value with a varchar value?

View Replies !   View Related
How To Create A Date From Integer
year=2004
month=1
day=1
how to create a date from that?
date(year, month, day) does not seem to work.

Thanks.

Jeff

View Replies !   View Related
Update On A Integer Field
Hi everybody,
Could someone help me ???
Iam starting with sql

My problem ..

Ihave a table Table_customer

In this table a column Table_customer.no
This column contain 8000 rows.
with customer numbers: like 30789
I would like to modify once all this row adding just before the number
a value to all these rows.This value will be a 60
So will have instaed of 30789 a 6030789
This column is an integer.

I have try a simple select wich give me this result
How i can do this with update ?
probably i have to convert fisrt to caracters

select kunr,nov_kunr=
'30'+ltrim(str(kunr))
from event
where kunr is not null

Thank

View Replies !   View Related
Dropping Integer Values
I have a simple flat file which I am trying to import which has 50k rows and about 50 columns. File is delimited (using a rather obscure multiple char delimiter) with text delimiting also.

When importing if I check the "Retain null values from the source" it will import the columns correctly but drop approx 7k of the rows.

If I uncheck this box it imports all rows, but on the second integer column in that target table all values are inserted as zeroes when there is at least 35k rows with a positive value.

I have put a data viewer on this import and it's also showing zeroes.

I'm tearing my hair out!!

View Replies !   View Related
Losing Integer Values
Following on from some problems I have been having with flat files, I seem to be stumbling from one issue to another.

I have coded my text file with some very contrived delimiters to ensure that my real data isn't tripping up the package. I am stripping all line break chars, tabs etc.

Now I seem to get all rows imported, but numerous integer columns within the file are being set to zero.

When I toggle the Keep Null options in the data flow task it will either import the row, but with the zeros, or not import the row at all. I have opened the file in text editors, and Excel and it all seems fine - in fact the same source file works OK with DTS/2000.

At a guess it seems as though these columns are seen as null by the package and so with the option switched on it defaults to zero - but they are most certainly not null in the file!! The table is a staging tabe and so is very basic in structure (no defaults or constraints)

SSIS seems a bit buggy or at the very least over sensitive to me - I am at the point of abandonment of it!!

Please has anyone seen similar issues?

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved