% Wildcard Include Nulls

Feb 28, 2006

I have a query with 4 parameters:

Name

Location

Employee Number

Officer Code

There are no null values in Name, location and Employee Number.  However, all employees do not have an officer code.  So in my query I use a where clause that says WHERE OfficerCode LIKE '%'+@Param4+'%'.  I use a % for the default value in my ASP.net data control.  The only problem is that the only records returned have non-null values in the OfficerCode field.  How can I use a wildcard for a default value and return all records (null and non-null)?

View 1 Replies


ADVERTISEMENT

How To Include The Nulls??

Sep 13, 2006

Hi, I have the following query stored:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)



Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...

As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.



The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.

Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.

I'd get

Prod | Qty | Week

1 23 1

1 12 2

2 10 3

3 22 1

3 15 2

3 12 3

3 4 4

Although this looks fine - what I actually need is:

1 23 1

1 12 2

1 0 3

1 0 3

2 0 1

2 0 2

2 10 3

2 0 4

3 22 1

3 15 2

3 12 3

3 4 4

Does that make sense?



Any ideas on how to do this?



View 13 Replies View Related

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related

Testing Permutations Of Nulls And Not Nulls

Feb 17, 2008

is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?

View 4 Replies View Related

Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS

May 15, 2008

I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.

So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.

View 2 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 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 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

Wildcard Searches

May 13, 2006

I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.

All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]

@dt_src_date = N'04/28/2006',

@chr_div = N'NE',

@vch_portfolio_no = 3,

@vch_prop_cat = N'core'

SELECT 'Return Value' = @return_value

GO

How can I set this so that it will wild card the value. For example rather than having to specify

@chr_div = N'NE', I could specify something like

@chr_div = N *, so it would show both NE and SW values in the result set.

Anybody point me in a direction here. I have tried % but that does not seem to work, I get a

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '%'.

Thank in Advance

View 4 Replies View Related

Wildcard In Simple Sql Search.

Mar 30, 2005

I'm very new to SQL so please forgive my ignorance...
I've made a simple .net search page which queries an sql database with the following query, (in a stored procedure):
SELECT     Category, Number, RegisteredUser, DeptName, Surname, Forename, Site, IDFROM         tblTelephonesWHERE     (@surname IS NULL OR                      Surname LIKE @surname) AND (@site IS NULL OR                      Site = @site) AND (@deptname IS NULL OR                      DeptName LIKE @deptname)
This works fine, as expected if i leave fields null or enter an exact match, but I (of course) have to add a wildcard in my search string for a wildcard search. For example, looking for 'duncan' i need to enter 'du%' or 'duncan'.
What I really want is for all searches to have wildcards behind them so only the first few characters need be inputted, and I could just search for 'd' or 'dun' without adding the '%' to get 'duncan'. I think I am aware of the implications of this approach and do want to go ahead as there are only about 850 records.
Any help or links to useful articles would really be greatly appreciated.

View 1 Replies View Related

WildCard (%) Search Issue

Dec 15, 2005

Hi all, I am currently using SQL Server 2000and am having some issues when searching a CHAR column and using the WildCard character '%'. The problem is that when I use this WildCard character, the results returned (if any!) are incorrect. For example, I know that there are many rows in the DB that have the following data in a column, 'ABERYSTWYTH CEREDIGION', but when I using the following SQL...'(WHERE (UPPER(AddrLine3) = 'ABERYSTWYTH%')'  I get no results returned.Is there a known issue with the WildCard character in SQL Server 2000?ThanksTryst

View 3 Replies View Related

Conditional Where Wildcard Problem

Jan 14, 2005

Hi,

I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

DECLARE@FirstName varchar (50)

SELECT @FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @FirstName WHEN '' THEN PhoneList.FirstName ELSE @FirstName END

This code produces no rows in the result. However if I change the second line to:
SELECT @FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.

I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

This is SQL Server 2k SP3 on Win2003 server.

thanks
Ben

View 1 Replies View Related

3 Tables Wildcard Searches

Apr 27, 2006

Hi,

I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

There are 3 tables involved, the jobs, the applicants and the applications.

I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

Thanks in advance.

View 2 Replies View Related

Compare Two Columns With WildCard

Aug 1, 2006

I am trying to compare columns in two tables with a wildcard character.

One table: Other Table:
----------------------
ID Col1 | ID Col2
---------------------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
-------------------------------
I want to writing something like:

SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

which obviously does not work.

basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

1A LIKE '1*'
1B LIKE '1*'

How can I do a comparison like this?

View 4 Replies View Related

CASE Statement Using Wildcard

Sep 12, 2006

im trying to use wildcard in CASE statement with no luck.

im use this:

SELECT CASE tb001
WHEN '%price%' then 'Price'
ELSE 'No price'

the colum is varchar

The expected result would be 'Price' in every row that contains price (with wildcard around) but every line shows 'No price'
Everything works fine if i don't use wildcard and put in the whole string.

thx in advance //Mr

View 2 Replies View Related

SQL Parameter Query && Wildcard

Sep 22, 2006

I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as

LIKE “*”&[Enter a portion of the part number]&”*”

Then we ported to a web based portal using MSAccess’s DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in

LIKE “%”&[Enter a portion of the part number]&”%”

So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this

LIKE @Enter_a_portion_of_the_part_number +n’%’

The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in

LIKE @Enter_a_portion_of_the_part_number +n’________%

However the Under Score only works with Alpha Characters and not Numeric.

So how do I write a SQL Server Query to perform like it did in Access?

View 2 Replies View Related

SQL 2012 :: SSL With Wildcard Certificate?

Oct 2, 2012

I have SQL Server 2012 and want to encrypt my connections by using a wildcard [URL] ssl certificate from a trusted party. After installing my certificate i want to selecti in in de SQL Server Configuration Manager but the certificate does not appear in the properties of protocols.

In older versions it was possible to add the thumprint of my certificate to the registry, but in this version that result into a sql server that cannot be started anymore.

View 7 Replies View Related

Replace Field Value Using Wildcard?

Feb 23, 2014

Is it possible to replace a string value that uses a wildcard? For example, I'm trying to update a field on a table and replace the field value where it starts with a specific two character pattern and remove it from the string. The field is a delimited string and I want to replace any occurrence of ;A0% or ;A0* with a null value. I don't want to replace the entire field with a null value.

Let's say my field value is this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;A0106;

I want the result to be this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;

I was trying several variations:

UPDATE myTable SET myField = REPLACE(myField,'XX%','')

View 1 Replies View Related

Wildcard Column Selection?

Sep 21, 2007

Is it possible to have some thing like:

SELECT columns beginning with 'a' from mytable

kinda thing?

View 7 Replies View Related

Storedproc For Wildcard Search

Mar 24, 2008

Hi someone please help me.

i have a serach page which have 4 textboxes.
passing this textboxes as parameters to storedproc iam searching the value.
filling atleast one textbox should fetch the value.

i have stored proc for searching it using normal column values but i want it do using wildcard search also.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN

DECLARE @MyTable table (CNo varchar(255))



INSERT @MyTable

Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))



--Now do your two selects

SELECT c.*
FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select r.*
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo
END

I WANT THE SEARCH TO BE DONE FOR WILD CARD CHARACTERS ALSO.

if the user enters lastname s*

using same storedproc can i insert wildcard search.

how can i do that please some one help me.

thanks

renu

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved