I have two tables. One table (Table 1) has a column containing comma
delaminated email addresses. The other table (Table 2) has a column
with just one email address in it. I need to perform a query that
joins the comma delaminated table (Table 1) to Table 2, when the single
email address in Table 2, is contained in the list of email addresses
in (Table 1).
I hope this isn't too cryptic, and I know comma delaminated lists are
bad, but I can't do anything about that.
I need a select statement that can perform this task. Anyone have any
suggestions?
I have a lot of News. Only i want to get last 10 news and piorty is important for me to change orders of the news. So i add a new coloumn in the table (Piorty int) ...
select top 10 NewsID, Piorty, Title, Spot from News order by 1 desc, 2 asc --
i want to get last 10 news than order by piorty (which piorty is bigger is be thre first) but in this query i just get last 10 news desc the piorty isn't imports even though i would get them order by piorty but last 10 news...
How can i do that ??? Please hellppp ... thnank you verry muchhh ...
we need to decide an architecture for Performance on a web site Search! I wanna use text service of SQL 2005 .But I am worried about the performance .... How should I design the system if I want the best perfomance and scalability ?1.Should I build a seperate coloumn in my every table and merge all the information into one coloumn and full text index that column.2.Put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.3.Make a different table and put _ID,_TYPE and _VALUE fields and search in that table with less coloumns.4.Seperate the full text database and search in a seperate db so that I can scale better?did anybody have a similiar problem ? Any books on full text search ?
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:
SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost, dbo.table2.dialed_digits FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)
I have one table 'tbUser' having coloumnsUserID,UserName,Category UserID UserName Category 1 Shailaja H 2 Anagha H 3 Raju L 4 Kiran L I want output as UserID UserName HigherCategory LowerCategory 1 Shailaja H 2 Anagha H 3 Raju L 4 Kiran L
Hello, Im a very new to SQL server etc, so please bear with me.
I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column.
I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column.
I am struck at one point. I am trying to this operation and not able to go further.
1. I have got the dataset to a variable in the control flow.
2. I am looping through the dataset based on a coloumn.
3. Now inside my For each loop i have a dataflow task.
4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @othervariable.
Now my question is
Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.
But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.
Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)
I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.
The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.
(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList
Result:
EventID ---------- 2
How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list.
Good evening! I have a problem. I have a namelist coming from a distribution list of an active directory. When I converted it to csv file, the members reside in just one column and separated by a comma. I want the names to be separated in a row one by one. I tried it on excel and I used the transpose column but to no avail. My last resort is to import it on sql but the names on the column was cut and not complete. Do you have any idea how to do this. Your help is highly appreciated.
Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d My output should be like this 1 a,b,d 2 c,g Can anybody help me I can I do this with the help of a cursor or any other way? Subhasish
I’m passing a comma separated parameter (home, world, child) into stored procedure. I have a Slitter function which is basically creates a table out of delimited list.
My stored procedure needs to find matched records in one of the table based on delimited list.
I have something like this:
SELECT * FROM Word WHERE WordName IN (SELECT * FROM dbo.fxSplitter('home,world,child, ',')
I would like to have my stored procedure be able to select rows, even if comma delimited parameter holds part of the name like this “hom, wor, chil� . Another words it will be SELECT * FROM Word WHERE WordName LIKE '%hom%' OR WordName LIKE '%wor%' OR WordName LIKE '%chil%'
I have a checkbox list on datalist as one column. when user selects more than one checkbox and click on apply. i concatenate IDs of checkboxes as '1,2'3' for e.g. and sending that to Stroe Procedure as varchar datatype parametrer. In Procedure i wanna update status of all three selected and i am using statement "update tbl set status=1 where pageid in('1,2,3'). It is saying it cannot convert varchar to int. How can i do this task? Thanks in advance.
How can I search for all the O'Connor's in my SQL Server database?eg: SELECT [Surname] ,[FirstName] FROM [myDB].[dbo].[Names] WHERE ([Surname] = 'O'Connor')
Is there a way to return a comma seperated list in a query?For example if I have this simple querySelect member_name From members It returns all the members in diff rows (lets assume we have 3)So the result will bemember_name--------------name1name2name3Instead of this I need to have the result in one row with values seperated by commas.member_name---------------name1,name2,name3I hope I am clear enough. Any help would be appreciated. Thank you.
Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.
What is the best way? I have a field of comma seperated article type id values in a users profile. This list is a set of values that sows the article types the person does NOT want to see. Each article has an article type id. I need to do a select joining the article table to the member table that only shows those article id's that are not in the comma seperated list. How would I do that in a sql statement?
Not sure the best way to do this. They are passing in a comma seperated string. I have a field that has comma seperated strings in it. I need to compare the 2 strings and do a select on that field that has any of the words in the string passed in. So if the pass... "car,boat,van"
and in the field 2 of the 3 records get sent back car,train,bike boat,truck,plane bike,plane,train
I would send them back the top 2 because the top one contains car and the second contains boat
I have Oracle query which seperates a text with commas to column data. Can we achieve this in SQL Server?
with t as (select 'abcd,123,defoifcd,87765,aoiwerwe' as str from dual) select level as n, regexp_substr(str,'[^,]+',1,level) as val from t connect by regexp_substr(str,'[^,]+',1,level) is not null;
CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED) WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5 BEGIN INSERT dbo.Numbers DEFAULT VALUES END
SELECT category, SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program FROM Test inner JOIN Numbers ON SUBSTRING( ',' + Value, Number, 1 ) = ',' and CHARINDEX( ',', Value+',', Number ) - Number <> 0 where Number <= Len(Value) + 1
But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on
Is there an easy way to handle a multivalue parameter where the values could have comma's? I have County and State in a single column and want to select one from the multivalue parameter but when the string is passed it is not being found because I am parsing the parameter on comma. Here is what the drop down list looks like.
