How To Pull Words Out Of A Column And Return As A Row

Apr 14, 2014

How can I pull words out of a column and return as a row?

column1 column2

12345 the#quick#brown#fox
12346 mmm#turkey

-result set

column1 column2

12345 the
12345 quick
12345 brown
12345 fox
12346 mmm
12346 turkey

View 5 Replies


How To Pull Few Records From A Table (Column) And Concatanate And Return As A Single Value.

Aug 6, 2007

Hi Gurus,
I have a table having sales records and there are more than one record per one customer. The sales table has a reference number like below.





I need to do a query and generate the following query.

CustomerID Ref
2 H_1123,H_2344,H_4322

Could someone help me on this.


View 3 Replies View Related

Counting No Of Words In A Column

Sep 13, 2006


for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.

for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this


View 11 Replies View Related

Queyr On Column With Multiple Words

Apr 2, 2006

I have a column that has multiple word delimited by a space or comma such as:

crime horror love

I want to perform a query so that a search for one of these words returns the record. THis is what I have so far:

SELECT columnname
FROM tablename
WHERE columnname LIKE 'strSearchVar'

This only returns records with one word in the column that matches. it ignores columns with multiple words.


View 6 Replies View Related

What Can I Do To Insert 9000 Words In Column

Feb 27, 2007

hi, i have varchar(8000) only acceptsin my there any possiblity to insert 10000 words in my row? i am using sqlserver2000. can any one please help me

View 5 Replies View Related

Reserved Words As Column Names

Nov 8, 2007

Hi there,

I'm trying to recreate a MS-SQL database in MySQL. One particular table has a column names "Precision", a reserved word in MySQL. I woudl really prefer to keep this name if possible as it will be referenced in all sorts of places. I've tried single and double quotes, that didn't work.

Is it possible to use reserveds word as column names? How?


View 6 Replies View Related

Using Reserved Words In Column Names

Jan 8, 2008

Here we go,

I have a great question for all of you.

What is the problem in using reserved words in table columns name? Any one have a compeling reason not to use?

Here is an exemple:

/ ******** Table ********/

When you query this table you should use the [ ] like this:

SELECT EntityId,[Name] FROM Entity

Anyone have any objection to this, with actual facts?

The question why use this?

This about the programing object in C#

Entity oneEntity = new Entity();

oneEntity.Name = "Test Entity";

It could be:

oneEntity.EntityName = "Test Entity";

Any one???

View 9 Replies View Related

RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'

Apr 25, 2007

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:

Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))




serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))


rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");


Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:

Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456

The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.

So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?

Please help.

Thank you.


View 10 Replies View Related

Choosing Between Two Column Values To Return As Single Column Value

Sep 14, 2007

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
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))  

View 1 Replies View Related

Return Column Name And Count

Nov 3, 2013

I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:

The following query:

SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID

Returns this result:


And the following query:

SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID

Returns this result:


I need to have both of those results returned, as a single result. Such as:

Category AssetQty


However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:

Column 't_Category.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.

View 5 Replies View Related

Return Dataset In One Column

Nov 7, 2006

Hi thereI have the following two tablesmainprofile (profile varchar(20), description)accprofile (profile varchar(20), acct_type int)Sample data could bemainprofile----------------prof1 | profile oneprof2 | profile twoprof3 | profile threeaccprofile--------------prof1 | 0prof1 | 1prof1 | 2prof2 | 0Now doing a join between these two tables would return multiple rows,but I would like to know whether it would be possible to returnacct_type horizontally in a column of the result set, e.g.prof1 | profile one | [0,1,2]prof2 | profile two | [0]I could probably manage this with cursors, but it would be veryresource intensive. Is there a better way?Regards,Louis

View 2 Replies View Related

I Want To Return The Value Of An Identity Column

Mar 3, 2008

I'm using C# to access sql server.
When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use?

View 9 Replies View Related

Return Record With MAX Column Value

Feb 26, 2008

The following query:

SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
tc NOT LIKE 'T%'
tc NOT LIKE 'R%'
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc

Returns a result like this:


How can I return the one record that contains the MAX(TCCount)?

Thank you for your help!


View 10 Replies View Related

Return SP In A SELECT Statement Column

Jan 13, 2005

I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.

I want the following output:

CatID | CatTitle | CatTree
001 | News | exec sp_DisplayTree(@CatID)

My code I tried doesn't work:

C.CatID As CatID,
C.CatTitle As CatTitle,
CatTree = (exec sp_DisplayTree C.CatID)
Cats As C WITH (nolock)

I cannot find a solution to my solution, please help...

View 3 Replies View Related

Return Only Part Of The Data In A Column?

Nov 20, 2012

I have a table with 2 columns ItemID and ReturnDesc. A sample of the data is:

ItemId ReturnDesc
1244 Did not want
1244 Wrong color
3426 Return came with ticket 426
3571 Ticket 584 was not included

The only information I want to have returned is the number following the text 'ticket'. How do I do this?

View 3 Replies View Related

T-SQL (SS2K8) :: How To Return Max From A Varchar Column

Jan 21, 2015

I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example


These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

View 9 Replies View Related

To Return Distinct Column Using INNER JOIN

Apr 23, 2008

Hello all,

I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !

Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6

I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6

I need to use SELECT * as there are many many columns and wish to save time :)



View 3 Replies View Related

Return Name Of Column Holding Highest Value

Apr 25, 2008

I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated

select clientID,
(select max(incomeValue)
(select earnings as incomeValue
union all
select unemployment
union all
select pensionRetirement
union all
select alimony
union all
select childSupport
union all
select dividendInterest
union all
select SS
union all
select SSI
union all
select SSDI
union all
select veteranBenefits
union all
select FIP
union all
select workStudy
union all
select other
union all
select otherHHWS) as income) as MaxIncomeValue
from tbl_income

View 12 Replies View Related

Assign Return Of A Query As Column Name

Feb 4, 2014

Is there a way to achieve that? I have a query with a subquery and I want to assign the return from the query as a column name in the subquery.

something like that where WorkDay is the return from the main query

as Convert(Varchar(10),WorkDay,110)

View 2 Replies View Related

Return Multiple Values In 1 Row Sep Column

Mar 20, 2014

I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.

So my result should look something like this:

EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child

How do I do this with SQL?

View 1 Replies View Related

Return Tables That Have Certain Column Names

Feb 28, 2008

Hi all,

From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this?

Please advise.

Thanks in advance.

View 7 Replies View Related

Return Rows Where Column Is Less Than 10 Characters

Jul 23, 2005

I need a query to simply return rows where a column has less than 10characters (nvar).thanks.rjl

View 2 Replies View Related

Analysis :: How To Return Column Name Without Qualifier Within MDX

Jun 4, 2015

I have just run an MDX query which returns a result set within Excel 2013. The column names are in the following format; -

[Dimension Name].[Hierarchy Name].[Attribute Name].[MEMBER CAPTION].

Where I just simply want the attribute name on it's own and simply the measure name for the measures.

View 9 Replies View Related

Using BCP To Load A Column Value With Carriage Return

Jan 24, 2008

I am using BCP and one of the tables in my SQL contains a column of type nvarchar(500) - whose data are actually email messages such as:

Sent: Wednesday, January 09, 2008 11:03 PM
To: Kathy Shouner
Cc: Brent Ford; William Dew
Subject: RE: Revision of presentation schedule

I support the recommended revisions.

When I open the datafile, it spans 6 separate lines - probably ending in carriage returns (not sure how to confirm this). Hence, I am having a problem importing it to my table. When I query the database, it appears as one line under that column - which is the way it should be.

My BCP command is something like this:

BCP "select email_msg, email_id from emails" QUERYOUT "c:email.txt" -c -T -S

I experimented with -w, -n, -N already but still am failing to get the proper output.
Please help... Thanks in advance.

View 1 Replies View Related

Return Max Value Of Specific Table Column

Aug 9, 2007

Hello Reporting specialists ... (GregSQL, Jarret, Lisa .....)

How can i get the max value of a single specific table column ??

Is this possible ? Or am i asking the impossible ??

Hopelessly, desperatelly remaining ...

see also

View 1 Replies View Related

Replace Carriage Return In Varchar Column

Jul 31, 2007


How do i remove Carriage return in a varchar column?


View 4 Replies View Related

How To Return Months For Column Labels In Crosstab

Aug 1, 2013

I want to line up across the top of a cross tab Jan-12 Feb-12 ... July-13 Aug-13 up to and including the latest month in the db. What's the best way to achieve this?

I currently create crosstabs with case statements as follows:

sum(case month(date) when 1 then value) as Jan,
sum(case month(date) when 2 then value) as Feb
from db

I figured you could create some sort of while loop for the case statements but I can't get the syntax right.

Or another strategy would be to use the Pivot method and build [Jan], [Feb], [Mar] etc through a loop?

View 3 Replies View Related

Return Subquery Rows As One Delimited Column

Oct 26, 2006

I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?

View 8 Replies View Related

How To Return Multiple Columns As Single Column

Mar 9, 2008

I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).

View 3 Replies View Related

Return Only Numeric Characters From A String Column

Dec 4, 2007

I have written this query to return only numeric characters from a string.

select *
from TableA
where isNumeric(Column_A) = 1

But I have discovered that the following

SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')

all return a value of 1. I do not want these in my result set.

If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.

I would like a simple way of only returning numbers in my resultset.

Thanks for looking at this.

View 2 Replies View Related

Derived Column Return WRONG Datatype.

Apr 18, 2006

Hi, friend,

l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

View 4 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

Using Stuff To Return A String Of Values From A Column Without A Cursor

Dec 13, 2007

I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')

SELECT @column_names

little help?

View 6 Replies View Related

Copyrights 2005-15, All rights reserved