Matching each value out of multiple values to single column value

 Hi,

  @whoposted in stored procedure holds value "agent  builder owner"  ( please note the space in between)

 My column  POSTED_BY  always holds one and only one value out of above 3.

Example:       

POSTED_BY 

owner

agent          etc...

If  All / any of the value hold by  @whoposted matches with value of POSTED_BY column, i want to show that row in gridview.

( Also, please note, str may hold different values depending on user selection. For example, it may hold only agent or 

agent, owner etc)

I want to modify my sql query..which works fine till now..I want to add condition for @whoposted

 

@whoposted holds value "agent  builder owner" and column posted_by holds only single value out of it. how can i modify my sql query??
 ALTER PROCEDURE dbo.flatsearch
(
    @type varchar(100),
    @unit varchar(20),
    @area int ,
    @loc varchar(100),
    @min int ,
    @max int ,
    @bathrooms int ,
    @bedrooms int ,
    @featuresofflat varchar(200),
    @whoposted varchar(200)
   
)
AS
SELECT ptype, rate, location,area,areaunit,
bedroom,features,bathroom,moreinfo,tel,mobile,email,postedby
FROM sellproperty INNER JOIN dbo.FN_SPLIT(@featuresofflat) r
ON sellproperty.features LIKE ('%' + r.SplitItem + '%')
where location Like '%' + @loc + '%' and rate >=@min and rate<=@max and bathroom>=@bathrooms
and bedroom>=@bedrooms and area>=@area and ptype LIKE @type

 

 FN_SPLIT FUNCTION

 ALTER FUNCTION FN_SPLIT (@Val VARCHAR(MAX))
RETURNS @Table1 TABLE (SplitItem VARCHAR(100))
AS
--This function expects a comma separated string
--and it splits the values on comma basis
--and returns a table having a column SplitItem
BEGIN
    DECLARE @ValTemp VARCHAR(100)
    DECLARE @ValT VARCHAR(100)
    DECLARE @Start int
    DECLARE @End int

    IF SUBSTRING(@VaL,LEN(@Val),1) <> ','
        SET @Val = @Val + ','
        
    WHILE CHARINDEX(',',@Val)>0
        BEGIN
            SET @Start = CHARINDEX(',',@Val)
            SET @End = LEN(@Val) - @Start
            SET @ValT = SUBSTRING(@Val,1,@start-1)
            SET @Val = SUBSTRING(@Val,@start+1,@End)
            INSERT INTO @Table1 (SplitItem) VALUES (@ValT)
        END
    RETURN

END

                      

                        

 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/5/2008 2:35:02 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

13 Replies
1248 Views

Similar Articles

[PageSpeed] 38
Get it on Google Play
Get it on Apple App Store

Please check http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

for slightly different version of the fn_split function that allows to use any character to split by (not only comma). 

 See modifications in your query I did.

SELECT ptype, rate, location,area,areaunit,
bedroom,features,bathroom,moreinfo,tel,mobile,email,postedby
FROM sellproperty INNER JOIN dbo.FN_SPLIT(@featuresofflat) r
ON sellproperty.features LIKE ('%' + r.SplitItem + '%')

INNER JOIN dbo.ufn_ALINES(@WhoPosted, ' ') whoP on Posted_By LIKE ('%' + whoP.Item + '%')
where location Like '%' + @loc + '%' and rate >=@min and rate<=@max and bathroom>=@bathrooms
and bedroom>=@bedrooms and area>=@area and ptype LIKE @type


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
10/5/2008 3:13:44 PM

Naom:

Please check http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

for slightly different version of the fn_split function that allows to use any character to split by (not only comma). 

 See modifications in your query I did.

SELECT ptype, rate, location,area,areaunit,
bedroom,features,bathroom,moreinfo,tel,mobile,email,postedby
FROM sellproperty INNER JOIN dbo.FN_SPLIT(@featuresofflat) r
ON sellproperty.features LIKE ('%' + r.SplitItem + '%')

INNER JOIN dbo.ufn_ALINES(@WhoPosted, ' ') whoP on Posted_By LIKE ('%' + whoP.Item + '%')
where location Like '%' + @loc + '%' and rate >=@min and rate<=@max and bathroom>=@bathrooms
and bedroom>=@bedrooms and area>=@area and ptype LIKE @type

 

Thank you Naom first of all.

I tried your code. But the problem is, even if I select Builder and column postedby  does contain Agent then also,

that row can be seen in gridview. I think the code requires little bit modification..not much..just little bit..

 

I copied only this much function :

CREATE FUNCTION ufn_Alines 
(@STR VARCHAR(8000),
@separator VARCHAR(16)=',')
RETURNS @TableArray TABLE
(ik INT IDENTITY,
Item VARCHAR(128))
AS
/* Splits passed string into items based on the specified separator string
Parameters:
@str - The string to split
@separator - The separator string ( comma is default)
Returns table variable with two columns:
ik int - Item number
Item varchar(128) - Item itself
*/

 
BEGIN
DECLARE @Item VARCHAR(128), @pos INT
WHILE DATALENGTH(@STR) > 0
BEGIN
SET @pos = CHARINDEX(@separator, @STR)
IF @pos = 0
SET @pos = DATALENGTH(@STR)+1
 
SET @Item = LEFT(@STR, @pos -1 )
SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
INSERT INTO @TableArray (Item) VALUES(@Item)
END

	RETURN
 
END
 
 
When I dont select any value ( agent,builder etc, ) I don't see any rows in gridview. I want to avoid this. 
 
Also, when i select all 3 values , the string which i am sending to stored procedure to assign value to @postedby
contains exact this format:    owner builder agent 
 

 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/5/2008 4:16:35 PM

 please check my above post

Also, where to write these lines in the function give at the link u mentioned

IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_Alines')
    DROP FUNCTION ufn_Alines
GO

( I Have omitted these lines as I got error function should start with Create or alter)


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/6/2008 2:45:41 PM

When you create a new query with the function code add these lines at the top.

For your original problem can you execute your SP directly in SSMS (without ASPX for now)? Can you make the query work ?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
10/6/2008 5:17:39 PM

Naom:
When you create a new query with the function code add these lines at the top.
 

If i add these lines i get error..that stored procedure must start with ALTER OR CREATE

Naom:
For your original problem can you execute your SP directly in SSMS (without ASPX for now)? Can you make the query work ?
 

I dont know how to execute stored procedure directly in SSMS.. but the function above is not giving expected results.

is there any other function ?

 

 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/7/2008 3:31:11 PM

I have changed the inner join in Naom's query to Left Outer Join, try the following. I am not sure whether your column name is Poste_by or postedby. However I used postedby as you used it in the select query. SSMS means SQL Server Management Studio i.e. where you are creating your stored procedure.

 

SELECT ptype, rate, location,area,areaunit,
bedroom,features,bathroom,moreinfo,tel,mobile,email,postedby
FROM sellproperty INNER JOIN dbo.FN_SPLIT(@featuresofflat) r 
ON sellproperty.features LIKE ('%' + r.SplitItem + '%')

LEFT OUTER JOIN dbo.ufn_ALINES(@WhoPosted, ' ') whoP on postedby = whoP.Item 

where location Like '%' + @loc + '%' and rate >=@min and rate<=@max and bathroom>=@bathrooms
and bedroom>=@bedrooms and area>=@area and ptype LIKE @type

Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
0
vijayakrishna
10/7/2008 6:08:20 PM

vijayakrishna:

I have changed the inner join in Naom's query to Left Outer Join, try the following. I am not sure whether your column name is Poste_by or postedby. However I used postedby as you used it in the select query. SSMS means SQL Server Management Studio i.e. where you are creating your stored procedure.

LEFT OUTER JOIN dbo.ufn_ALINES(@WhoPosted, ' ') whoP on postedby = whoP.Item 

Hi.. Now i cannot see results. can we use dbo.FN_SPLIT function in this case ? It is at least reliable.

I have used dbo.FN_SPLIT function before when column used to contain comma seperated values.

Here the only difference is column contains single value and we want to match each value in string (seperated by space) 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/7/2008 6:38:21 PM

I verified the function ufn_ALINES and it is working fine. To see it as working, run the following query in your SQL Server Management Studio.

select * from dbo.ufn_ALINES('Builder Owner Agent', ' ')

And at the same time you can run your stored procedure in the same place something like using the following

dbo.flatsearch '1BHK','First Floor', 'unit,500,'Andheri',1000000,2000000,2,2,'feature1,feature2,feature3','agent builder'

If user does not select any option for @whoposted then assing the 'Builder Agent Owner' to it and send it to your stored procedure and use the inner join itself as posted by Naom. 


Vijaya Krishna Paruchuri
Please remember to click “Mark as Answer” on the post that helped you. This credits that member, earns you a point and marks your thread as resolved.
0
vijayakrishna
10/7/2008 7:31:04 PM

 i want to edit my last post..

 ** I can still see results ... but results ( outcome) dont change depending on @whoposted value. 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/7/2008 7:35:10 PM

vijayakrishna:

I verified the function ufn_ALINES and it is working fine. To see it as working, run the following query in your SQL Server Management Studio.

select * from dbo.ufn_ALINES('Builder Owner Agent', ' ')

And at the same time you can run your stored procedure in the same place something like using the following

dbo.flatsearch '1BHK','First Floor', 'unit,500,'Andheri',1000000,2000000,2,2,'feature1,feature2,feature3','agent builder'

If user does not select any option for @whoposted then assing the 'Builder Agent Owner' to it and send it to your stored procedure and use the inner join itself as posted by Naom. 

 

I do not know how to execute query in SQL SERVER MANAGEMENT STUDIO...If possible please let me know.

I tried  inner join query of NAOMI again.. but the thing is, if i select AGENT and row contains value "OWNER" for the

postedby column , then also, i can see that row in gridview. Actually, that row should not be seen ...

this is my problem.


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/7/2008 7:48:43 PM

 

following solution worked...

using  CharIndex(ColumnName, @str) <> 0

example :

Declare @str varchar(1000)

Set @str = 'aaa bbb ccc'

Select * From Table

Where CharIndex(ColumnName, @str) <> 0

 

** wonder Naomi didnt bother to reply once he got to know his solution not working..neways..my problem solved now..thanks to both of u 


Please Mark the post as "ANSWER" if it helps you
0
closeguy2005
10/9/2008 6:41:11 AM

I got swamped with work, didn't have time to check forums. Anyway, you should have checked the query as a standalone (In SSMS new Query and just run this query).


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
10/10/2008 12:22:56 AM

I just found I had a message from you asking to check this thread. I apologize, I didn't notice it before. Anyway, this thread seems to be resolved.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
11/7/2008 2:01:26 AM
Reply:

Similar Artilces:

If Not value or value then....
Hi all,I want to write a simple IF Not then statement in ASP.net 2.0 and I would like some help please.  If Not ntuser = "DOMAIN\ntuser" Or ntuser = "DOMAIN\ntuser2" Then Panel1.Visible = False End If Doesnt work, can you tell me where i'm going wrong please?many thanks.  The first place you're going wrong is in telling us it "doesn't work" without providing any details. JeffPlease: Don't forget to click "Mark as Answer" on the post that helped you. That way future readers will know which post solved your issue. Use Not as stated below. It ...

Choosing between two column values to return as single column value
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid       user2_guidI am trying to write a query to return a single list of all a users' friends in a single column.  Depending on who initiates the friendship, a users' guid value can be in either of the two columns.  Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way...  Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN ...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

SQL QUERY replace NULL value in a row with a value from the previous known value
Hi!! I have a table with no unique ID, I created an Id with ROW_NUMBER () OVER (ORDER BY column asc) rank and created a stored procedure with the rank and table. In this stored procedure I want to create an extra column that checks if the value from another columns contains value = 100 rank,Ifnummer,followingnr,productnr testing 2313,'6497269','001' ,412 , NULL 2314,'6497269','002' ,413 , NULL 2315,'6497269','003' ,100 , yes 2316,'6497269','004' ,430 ,NULL ...

How do I check to see if a value returned from a sql datasource equals a listitem value
I'm trying to see if this listitem equals a value returned in my sql data source, but I get keep getting a error saying  'Item' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.    How do I check if the employeeID returned is equal to the li.value? For Each li As ListItem In BillingEmployeeID.Items If li.Value = getEmployeewithMostOpenClient.Item("employeeID") ThenxxxxxxxEnd If NextNever make important decisions on a Monday! Try Items.FindByValue() or Items.FindByText() method. Something like this... string szValueFromDataso...

Publishing single valued SQL entries to multi-valued eDir (ID Vault)
I'm running the JDBC 1.6.4 driver to connect to a MSSQL 2000 database. I'm using this database to collect and publish updates to the ID Vault. I'm not using this connector to manage SQL accounts. I found the following Subscriber Output Transform Policy to map multi-valued attributes to single-value fields. </xsl:template> <!-- Handles mapping of multi-valued attributes to single-valued fields --> <xsl:template match="query[@class-name='emp']" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver. jdbc.u...

Publishing single valued SQL entries to (Overwrite) multi-valued eDir (ID Vault)
I'm running the JDBC 1.6.4 driver to connect to a MSSQL 2000 database. I'm using this database to collect and publish updates to the ID Vault. I'm not using this connector to manage SQL accounts. I found the following Subscriber Output Transform Policy to map multi-valued attributes to single-value fields. </xsl:template> <!-- Handles mapping of multi-valued attributes to single-valued fields --> <xsl:template match="query[@class-name='emp']" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver. jdbc.u...

Single Value to Multi Value attribute
Hi all I have an attribute 'source', in my eDirectory dn: cn=schema changetype: modify add: attributeTypes attributeTypes: ( source-oid NAME 'source' DESC 'to capture the source where the user or group is pulled from' SYNTAX 1.3.6.1.4.1.1466.115.121.1.15 SINGLE-VALUE ) - I need to modify this attribute from Single valued to Multi-value attribute, - I also need to modify the name of this attribute from 'source' to 'uniqueSource' can any one please give me the exact syntax for the the above modifications ...

Convert Row values into Columns values
 Query : - Select RB_BE_PARAM_VALUE,RB_BE_PARAM_FLAG From RB_BE_FE_FID_Details where RB_BE_PARAM_FLAG like 'EMPLOGIN%' Result:- RB_BE_PARAM_VALUE RB_BE_PARAM_FLAG 3 EMPLOGIN_INVALIDATTEMPTS 5 EMPLOGIN_PWDEXPIRY 7 EMPLOGIN_ACTIVEDAYS 10 EMPLOGIN_SESSIONTIME   I want folloewing result:- colname Colname Colname Colname 3 5 7 10 ...

ICallbackEventHandler and passing multiple string values to server and receiving multiple values
Hello, I need some help with ICallbackEventHandler and was wondering if some experts can share their knowledge on this. Thanks in advance. I am retrieving results from a web service that returns xml nodes. In order to return XML nodes to client, I have converted xmlnodes into string (string concatenation and returned one string to the client). Is there a way to return may be xml or JSON or even a string array using ICallbackEventHandler. Here is my complete code. Imports net.ecubicle.www Imports System.Xml.LinqPartial Class driving_web_service Inherits System.Web.UI.PageImplements ICa...

Insert multiple values(multiple columns) based on single dropdown list selection.
What I would like to accomplish:  I have a dropdown list that is an entityref(populated by values from PK in separate table). When user makes selection in dropdown list, then clicks “Insert”, I would like the selected value to be inserted into the applicable FK column, but also would like to have a value equal to the integer selected, multiplied by 50, inserted into another column of the same table. Example with user selecting “2” in the dropdown list, inserting into Table b:table a: Column: Numbers(PK) (1, 2, 4, 8) Table b: Processors(FK) Inserted: “2”Table b: Factor Inserted: ...

How to insert multiple values in a single column
I need to display output as shown below. In Col1, Col2 and Col3 I want to insert values from subqueries. But do not want to use sub-reports... is there any alternative to subqueries. In Col1, Col2 and Col3 there can be any number of values. Company Bank Col1 Col2 Col3 AstroTech ICICI 1 2 3 5 3 4 MindTree HDFC   5 4 8          Pardeep Bogra (MCA, MCAD)Systems Develper  why don't you want to use a sub-report? It takes very long time, because i have 17 colu...

Multiple values in a single datagrid column
I need to pass a 2nd variable to my hyperlink control (I am using the pop up control from metacontrols, but its the same as a normal hyperlink). I have written for a single field, but need to add a 2nd pulled from the data. How do I do it? This is what I have so far, but I need to add another parameter. <mbrw:RemoteWindow id="Remotewindow2" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Username") %>' NAME="Remotewindow2" WIndowWidth="720" WindowHeight="400" NavigateUrl='<%# DataBinder.E...

Can a DataSource set the default value in a DropDownList (or the list fetch the value from a DataSource)?
Hi I have a DropDownList that gets its values from a DataSource-control. Nothing magic so far. What makes me scratch my head is that I need to set the default (selected) value in the drop down. As the values come from a data source, I do not know the key/value/index of the default I. Is there a possibility to make the drop down "ask the data source" which key should be the default (that is, only if the drop down is populated the first time, of course)? Background: I have an application where an administrator can set the "standard" month that the application s...

Web resources about - Matching each value out of multiple values to single column value - asp.net.sql-datasource

Tile-matching video game - Wikipedia, the free encyclopedia
A version of Tetris , an early tile-matching game. Tiles drop from above, and the matching criterion is filling a horizontal line. In many recent ...

RentalRoost Brings Facebook Into Process Of Matching Up Home Seekers, Available Properties
A wide range of opinions exists on whether all of the data Facebook has on its users is a good thing, but it could turn out to be a very good ...

SPMD Kenshoo launches Intent-Driven Audiences, matching search ads to Facebook
Kenshoo , a Facebook Strategic Preferred Marketing Developer, announced Wednesday the launch of Intent-Driven Audiences . This new tool matches ...

App Store - Color Memory Match - a Free Matching Card Game
Get Color Memory Match - a Free Matching Card Game on the App Store. See screenshots and ratings, and read customer reviews.

ATO ramps up data matching, tells Uber drivers to register or we're coming for you
Uber and other ride-sourcing drivers are being warned by the Tax Office to declare their income and pay GST before the end of the month.

Brisbane Lions fury at pick for Matthew Leuenberger, 'consider' matching Bombers
The Brisbane Lions have criticised free agency compensation and&nbsp;say they are considering matching Essendon's contract offer for Matthew ...

Programmatic Goes to the Dogs in Saatchi Pet-Matching Effort
Programmatic advertising is about to go to the dogs. In a campaign breaking in November, a Beverly Hills, Calif.-based animal shelter will use ...

Apple is looking to increase the music library matching limit to 100,000 tracks
... Tale , and Take a ride on the world’s most famous train with Orient Express History . Apple is looking to increase the music library matching ...

New System Center Configuration Manager to be “as a service,” matching Windows 10’s pace
The days of service packs and cumulative updates will soon be behind us.

Is the skill of rev matching being lost to computers?
Filed under: Driving , Technology , Performance , Open Road Manual transmissions are becoming scarcer by the day, making rev matching a rare ...

resources last updated: 11/20/2015 1:54:55 PM