Wildcard For Integers Or Something?

Dec 10, 2001

I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.

Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.

Does anyone have any ideas here?

Thanks,
Al

View 2 Replies

Adding Integers

Feb 15, 2007

The code below has this line
SET @SOGallons = @ODTGallons

I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.

I've tried
SET @SOGallons = @SOGallons + @ODTGallons

SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>

View 3 Replies View Related

Integers Which Are Greater Than Ones Before Them

Aug 29, 2013

I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.

How do I query just the changes?

Example dataset:

idrating
15
25
35
45
56
66

[code]....

There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:

idrating
45
56
97
118
189

I use Microsoft SQL 2008

View 2 Replies View Related

Count Using Non Integers

Feb 23, 2007

1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01

I am not sure how to count using non integers

Thanks

View 10 Replies View Related

Integers && Dynamic Allocation

Mar 10, 2004

Here is what I am trying to do...
I want to goup "members" togethers in a "group."

A table for members and a table for groups.

each containing coluns...

but inside Groups I would like a column that contains ID numbers for the members that be long to that group.

Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.

Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.

Hope this makes sense

View 1 Replies View Related

Is There A Function Like SUBSTRING But For Integers?

Apr 14, 2006

I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?

View 5 Replies View Related

Comparing Two Integers And Returning A Third

Oct 20, 2006

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

View 2 Replies View Related

Converting Integers To DateTime

Jun 12, 2007

It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.

I have input parameters @months int, @days int, @years int in a stored procedure.

All I want to do is to get a DateTime variable out of them.

DECLARE @dated DateTime.

Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!

No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?

It is rudiculous. I've tried dozens of variants. Please help.

Thanks.

View 10 Replies View Related

Decimals Converting Back To Integers? What's Going On?

Feb 23, 2007

I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!

View 5 Replies View Related

Dividing Integers To Produce A Percentage

Aug 27, 2003

I'm embarassed I haven't figured this out already but here goes.

Lets say you need a percentage from dividing two integers for example

"select 2000/4000"

This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)

I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.

As always, thanks VERY MUCH for the kind advice.

View 2 Replies View Related

Replace All Integers With Continuous 6 Or More Occurrences With X

Oct 4, 2012

I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.

create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO

EXPECTED RESULT:

1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS

drop table t1

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

View 9 Replies View Related

Derived Column Can't Add 8-byte Integers

Jun 7, 2007

I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:

Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer {DT_I8]

The error I get:

The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.

Then I try the expression:

(DT_I8)[ID] + (DT_I8)840230000538058

and

[ID] + 840230000538058

and get the same error.

What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?

Thanks,

Michael

View 6 Replies View Related

Casting DT_WSTR To Integers In SSIS

Jul 5, 2007

Hello, all.

Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?

(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))

I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.

Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.

Thanks for any help.

View 7 Replies View Related

JOINS And Integers Vs. Indexed Strings.

Jun 15, 2006

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?

TIA,

barkingdog

View 1 Replies View Related

REPLACE Integers With Text Data

Jun 14, 2006

I am working with a database named ��Documents�? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text
2 = Report
3 = Background
4 = Index

I would like to create a new column named ��DocType�? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN ��DocType�? WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go

But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: ��Incorrect syntax near 'REPLACE'.�?

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: ��Incorrect syntax near 'REPLACE'.�?

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

View 3 Replies View Related

Can I Return A Range Between Two Integers On A Recordset?

Feb 15, 2008

I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc

Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc

I'm using MSSQL 2005

tks

View 1 Replies View Related

Concatenating Numbers/Converting Dates To Integers

Feb 24, 2004

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.

View 9 Replies View Related

Using SET To Correct Integers In A Dbo.Table: Incorrect Sysntax Near The Keyword ‘SET’?

Jan 31, 2008

Hi all,

I have a wrong ��dbo.Samples�? table:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1 Blueriver01 Soil QA VOCs 1
7 Greentree01 Water Primary VOCs 1
8 Greentree02 Water Duplicate VOCs 1
9 Greentree03 Water QA VOCs 2
10 Greentree11 Soil Primary VOCs 1
11 Greentree11 Soil Duplicate VOCs 1
12 Greentree11 Soil QA VOCs 3
13 Redrock01 Water Primary VOCs 1
14 Redrock02 Water Duplicate VOCs 1
15 Redrock03 Water QA VOCs 2
16 Redrock11 Soil Primary VOCs 1
17 Redrock12 Soil Duplicate VOCs 1
18 Redrock13 Soil QA VOCs 3

I used the following sql code to correct the wrong ProjectIds:

USE ChemDatabase
GO
ALTER TABLE Samples
SET ProjectID = 4 WHERE SampleID = 7
SET ProjectID = 4 WHERE SampleID = 8
SET ProjectID = 5 WHERE SampleID = 9
SET ProjectID = 4 WHERE SampleID = 10
SET ProjectID = 4 WHERE SampleID = 11
SET ProjectID = 6 WHERE SampleID = 12
SET ProjectID = 7 WHERE SampleID = 13
SET ProjectID = 7 WHERE SampleID = 14
SET ProjectID = 8 WHERE SampleID = 15
SET ProjectID = 7 WHERE SampleID = 16
SET ProjectID = 7 WHERE SampleID = 17
SET ProjectID = 9 WHERE SampleID = 18
GO

I got the following error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.

Please help and tell me what it is the right syntax for my ��SET��
used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.

Thanks,
Scott Chang

View 4 Replies View Related

Wildcard Use

Jun 30, 2006

Hello everyone and thanks for your help in advance. I am working on an application that does a property search off of a database that contains approximately 40000 records. The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc. I set up a stored procedure to query the databse. if one of the parameters is not specified, i simply pass it a "%". However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even if only one or two are returned. I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this. Any help on this topic would be grealy appreciated.

View 3 Replies View Related

Wildcard

Sep 9, 2006

hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated

View 7 Replies View Related

Looking For A Wildcard

May 23, 2003

hi,

how do i contsruct a SQL query to search for the % character in a data field?

thanks

View 1 Replies View Related

CONTAINS And Wildcard

Oct 29, 2007

Is it a good idea to have multiple contains? I have this query:Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')Also, I tried this, didn't return anything:Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')Both Col1 and Col2 has the string 'Africa' and 'African' in it.--sharif

View 2 Replies View Related

Using Wildcard

Aug 22, 2006

alter procedure sp_ADsearch @tbname varchar(1000),@searchproduct nvarchar(500)

as

declare @sqlstr nvarchar(1000)

set @sqlstr='select * from '

set @sqlstr= @sqlstr + @tbname

set @sqlstr= @sqlstr + ' where name like '

set @sqlstr= @sqlstr + '%' + @searchproduct + '%'

exec sp_executesql @sqlstr

--

exec sp_ADsearch product,ee

when executing the procedure i am getting an error "Incorrect syntax near 'ee'.".

whats wrong with the syntax? how to use % along with variables

View 5 Replies View Related

Wildcard Used For SQL IN Clause

Dec 30, 2006

I am trying to control how users view records. I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records)
I want to create an 'authorization table' so that different users can see different records. I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause? Does anyone have any suggestions. Perhaps I am taking the wrong approach. I would appreciate any guidance.

View 2 Replies View Related

Wildcard In The WHERE Clause

Jan 28, 2007

Hello,
I'm trying to pull some data from a table with the option to filter on 2 columns.
I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT *
FROM City WHERE CityName= @CityName AND State= @State
ORDER BY CityName
For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters.
Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters.
The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong.
Thanks.

View 4 Replies View Related

Wildcard Misuse?

Oct 5, 2006

I've got a text field with a list of dates in this format:

10/31/2006
11/2/2006
11/4/2006
11/5/2006

The field can contain multiple dates (as listed above). I need to query the db and retrieve dates by month and year. For example, all fields containing 10/%/06 or 11/%/%06.

My query:

SELECT * FROM tblCalData WHERE visible='1' and approved='1' and eventDates LIKE '11%06%' ORDER BY eventDates DESC

so even though a field contains dates in October and November, selecting all November dates, in this example, don't appear. The query seems to be looking only at the first date.

Is this possible? Is there another/better way to do this?

Thanks!

:grimey

View 14 Replies View Related

Is There A Way To Use Wildcard Characters...

Feb 1, 2006

to reference columns.
i have a table that has a column that is repeated 25 times, sort of.

table_1
tbl_ID (PK)
tbl_c_1 char 5
tbl_c_2 char 5
tbl_c_3 char 5
tbl_c_4 char 5
tbl_c_5 char 5
....
tbl_c_25 char 5

everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way?
I cannot change the table.

View 6 Replies View Related

WildCard If Not Value Selected

May 7, 2008

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

View 11 Replies View Related

Wildcard In Query?

Dec 6, 2007

I screwed up my database and double added data to a text field.

For example I need made this:

'http://192.168.200.1/images/' +PATH+ '.jpg'

Look like this:

'http://192.168.200.1/images/http://192.168.200.1/images/12345.jpg'

How can I query to reset this?

update perimage
set PERIMAGE_PATH = 'http://192.168.200.1/images/' +PATH+ '.jpg'
WHERE PERIMAGE_PATH <> 'http://192.168.200.1/images/' +PATH+ '.jpg'

That just makes the problem worse

View 3 Replies View Related

Wildcard In Query?

Dec 11, 2007

I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:

INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> ' '
update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*

What is the proper code to do this. I know the last line does not work. Thanks

View 7 Replies View Related

Using Wildcard With Parameters

Oct 24, 2007

The where statement on my stored proc is as follows:

Where actv.ProjID + '-' + actv.Activity = @project

@project is something the user provides in the form of "xxxx-xx-xx-xx". I would like to use a wildcard, so I changed my where statement to the following:

Where actv.ProjID + '-' + actv.Activity = @project + '%'

But this returns nothing and I am not sure why. I don't get any errors, just no results. Anyone got any ideas as to why?

Thanks in advance!

View 9 Replies View Related

Wildcard Ssl Certificates?

Apr 27, 2007

Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks

View 1 Replies View Related

Query On SQL CE 3.5 With Wildcard

Jan 9, 2008

Hi,

I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException.
Here is my query with parameters :
Query: SELECT "PARAGRAPHES"."PARA_ID" AS "Numero","PARAGRAPHES"."PARA_SECT_ID" AS "Section","PARAGRAPHES"."PARA_NUMERO" AS "Rang","PARAGRAPHES"."PARA_GROUPE" AS "Groupe","PARAGRAPHES"."PARA_ENTITES" AS "Entites","PARAGRAPHES"."PARA_LBL_COURT" AS "Court","PARAGRAPHES"."PARA_LBL_LONG" AS "Long","PARAGRAPHES"."PARA_ACTIVE" AS "Active","PARAGRAPHES"."PARA_CONDITIONS" AS "Conditions","PARAGRAPHES"."PARA_PARENTS" AS "Parents","PARAGRAPHES"."PARA_QUESTIONS" AS "QuestionsConditions" FROM "PARAGRAPHES" WHERE ( ( ( ( "PARAGRAPHES"."PARA_SECT_ID" = ? AND ( ( "PARAGRAPHES"."PARA_ENTITES" LIKE ?) OR "PARAGRAPHES"."PARA_ENTITES" IS NULL))) AND "PARAGRAPHES"."PARA_ID" IN (SELECT "DOSSIER_LINKS"."DOLI_PARA_ID" AS "Paragraphe" FROM "DOSSIER_LINKS" WHERE "DOSSIER_LINKS"."DOLI_DOSS_ID" = ?) AND "PARAGRAPHES"."PARA_NUMERO" = ?))
Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52.
Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%".
Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
and my error :
[System.FormatException] = {"@Entites2 : %D% - FormatException"}

If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.

ThanksFabien

View 1 Replies View Related