Concaternate String From Same Column

Jan 21, 2008

I want to concaternate all rows that has the same ID and update another table.

Table1:
ID: COL1
1 txt1
1 txt2
1 txt3
2 txt7
3 txt5
3 txt6

OUTPUT:
ID: COL1:
1 txt1, txt2, txt3
2 txt7
3 txt5, txt6

How do I manage this, have tried self join but with no luck.

View 2 Replies


ADVERTISEMENT

Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation

Mar 5, 2007

Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.

When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.

When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.



Any help in this will sincerely be appreciated.


Thanks

View 7 Replies View Related

Max Of String Column

Nov 8, 2006

I having a column formatted as char(12) being used for indexing. I am not getting the correct max value back from the statement:
"SELECT MAX(ID) FROM STAGING_TABLE"

i.e.
ID column contains 45,999,1726..The returned val for the query above is 999. Any ideas? TIA

View 1 Replies View Related

How To Get Csv String From Column

Dec 14, 2007

Hi All,
I have a table which contains values like this :

Column 1 Column 2

A 1
A 2

A 3
B 1

B 2



In need the output in this format:
Col1 Col 2
A 1,2,3
B 1,2


Can anyone help me with this ????

View 3 Replies View Related

SELECT(ing) One DB Column Value Into A String In C#... How?

Jun 18, 2007

hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;     protected void Button1_Click(object sender, EventArgs e)    {        SqlDataSource profilesinsert = new SqlDataSource();        profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString();        profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text;        profilesinsert.InsertCommand = "INSERT INTO ProfileComments  (Approved) VALUES (@Approved)";        profilesinsert.InsertParameters.Add("Approved", "yes");        profilesinsert.Insert();    } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :) 

View 19 Replies View Related

Column Max Size For String

Nov 13, 2003

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

Grant

View 2 Replies View Related

Strip String From Csv Column

Mar 26, 2004

I have a column of 5 comma-separated-value strings:

stringA, stringB, stringC, stringD, stringE

The strings are GUID's with the hyphen stripped and made all uppercase so they are completely random. I need to be able to remove any one of the strings including the comma, in a stored procedure and I am not sure how to accomplish this.

SELECT tickets
FROM users
WHERE CONTAINS (tickets, @ticket)

IF @@rowcount > 0
REMOVE STUFF HERE
SET @valid = 1
ELSE
SET @valid = 0

So if stringB gets passed in as @ticket then the new value in the column would be :

stringA, stringC, stringD, stringE

Any help is greatly appreciated.
Thank you
dave

View 9 Replies View Related

Concatenating String Column

Aug 13, 2003

Hi All,

I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.

For eg:

column1
abc
def
hij
klm
nop

is it possible to write a select statement which would return

result
abcdefghijklmnop

as a result?

TIA
Ketan

View 6 Replies View Related

Search For A Column With Certain String

Oct 1, 2013

I have access to a database's web front end and a limited amount of access to the server.

I am trying to find out what column a certain string is in.

I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...

I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.

I don't have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"...

View 14 Replies View Related

Spliting Up A String In A Column

Apr 3, 2008

Good Morning,

I'm trying to write a scrip to compate two columns in two different tables to each other (billing_payee to Debtor_name)

The problem i have is that the info in the billing_payee column is CLIENT RE DEBTOR INITAL SURN and the infor in Debtor_name column is [i]DEBTOR TITLE NAME SURN[i]

What i need to be able to do is select all from Billing_Payee table where the Billing payee [i]DEBTOR INIAL SURN[i] is not the same as the info in Debtor_name.

I somehow need to split out the inial - first letter of debtor name and surname.

If i can't do it this way i need some other way of comaparing the two.

I am fairly new to t-SQL however have a pretty good grasp of it.

Please let me know if there is anything else i can provide to make this any easier any help is much appreciated!!

View 5 Replies View Related

Separate String To Three Column

Mar 2, 2015

How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.

View 4 Replies View Related

Sorting Of A String Column

Mar 7, 2008

I have the column of type string in the database
Following is the data in that column
1
2
11
12
21
abc
If i sort the table with the help of this column then the output come in the following manner
1
11
12
2
21
abc

can i improve the order actually i want the output like 1 2 11 12 21 abc

View 2 Replies View Related

Replace A String Column With 'Y' Or 'N'

Nov 12, 2007

Hi,

I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.


=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')

=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')


Would you please tell me what's wrong? Your suggestions would be appreciated.

Thanks alot.

View 3 Replies View Related

How Can I Put A Newline Within A String Column?

Apr 20, 2006

I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.

Any ideas?

View 5 Replies View Related

Compare 2 Piece Of String In A Column

May 30, 2008

Hi All,
I have a column in my table like so:
'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 '
How do I check in SQL if any pieces have the first 3 character the same.  In the above case, L2A is present twice.  I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once.
Any help is appreciated.
Thanks

View 3 Replies View Related

Removing Partial String From Column

Oct 11, 2005

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the front

View 5 Replies View Related

UNQIUE Constraint On String Column?

Jan 21, 2008

What do you guys think about creating a unique constraint on a string column in a database?

Currently, I'm enforcing uniqueness through the stored procedure that inserts rows into the table. E.g.

PROCEDURE addRow( name )

DECLARE r INT;
SELECT COUNT(*) INTO r FROM foo WHERE foo.foo_name = name;
IF r = 0 THEN
INSERT INTO foo (foo_name) values(name);
ELSE
// Not unique throw an error

END PROCEDURE


What do you guys think?

View 12 Replies View Related

SELECT Part Of A String In A Column

Dec 23, 2013

I'm trying to form a query that will select part of a result.I'm trying to take out the ending of results that end in '-PR'.

Example: The original result is 'Jbbx32-PR'. I want to select it as 'Jbbx32'.

View 5 Replies View Related

Find String In Any Column In Any Table

Sep 13, 2001

quote:Another question is how to find a string in any column of any table.
(Above is a hint as to one way)
I had to do this because a system was sending out a not very impressive email to users ad we couldn't find what was triggering it.


I have run into a similar situation. Care to share your solution?

Cat

View 1 Replies View Related

Derived Column - DateTimeStamp From String (dd-mmm-yy)

Jun 7, 2006

I have come across something strange. I was wondering anyone else had the same problem or is it me?

I have a source file as shown below:
1,30-Feb-06,3,Test
2,20-Feb-06,5,Test1

Second column is defined as String(9) in the flat file source. Then I have a Derived Column between source and Trash destination. The Derived Column adds a new column to the datd flow. The expression is as shown below :
(DT_DBTIMESTAMP)(SUBSTRING(Col2,1,2) + "/" + SUBSTRING(Col2,4,3) + "/" + SUBSTRING(Col2,8,2))

The data is loading into the database successfully, where I was expecting it to fail. I placed a data viewer and saw the following result set.

Source (Col2) Derived Col
------------------- ------------------
30-Feb-06 06/02/1930 00:00:00 - WRONG result set
20-Feb-06 20/02/2006 00:00:00 - RIGHT result set

Can anyone repeat this problem or am I missing something? I have got SP1 and Hotfix installed.

Thanks

Sutha

View 1 Replies View Related

How To Specify An Empty String As A Default Column Value?

Dec 6, 2007

In the Column Properties window's 'Default Value or Binding' property, how do you specify an empty string?

Thanks.

View 5 Replies View Related

Extrach Part Of A Column String Into Another Field

Feb 6, 2001

hi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column.
thanks

Ahmed

View 2 Replies View Related

Inserting Into ONE Column A Comma Delimted String??

Dec 1, 1999

I have a string of comma delimited values such as the following:

1,2,3,4,5

I have a table with ONE column only. How can I do an insert statement which will insert each of these values?

If I type the following:
"INSERT INTO tabletest SELECT 1,2,3,4,5"

I get this error:
"Insert error: column name or number of supplied values does not match table definition. "

This makes sense because I do not have 5 columns I only have 1. But how can I get around this?


Any help is most appreciated! Thanks in advance...mary

View 1 Replies View Related

Setting Default Value (concatenated String) Of Column Using UDF

Oct 23, 2005

Hello,

I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).

So basically the SysInvNum column for this row should read '200500015'

When I run a basic query using the CAST or CONVERT functions like this:

SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform

OR

SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform

I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.

I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:


CREATE FUNCTION GetSysInvNum()
RETURNS varchar
AS
BEGIN
DECLARE @maxcaseid Int
DECLARE @sysinvnum varchar

SELECT @maxcaseid = max (caseid) from caseform
SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid
RETURN @sysinvnum
END


The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".

Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?

Thanks in advance

'Wale

View 10 Replies View Related

SQL Server 2012 :: Getting String Out Of Data From A Column?

May 7, 2015

I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below

Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information

sample data from system_id_information column

########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>

[Code] .....

I am looking output of two columns, which are bolded

product_id snumber
654081-B21 MXQ43905SW

for serial number this is common

before string :HostSystemIdentificationInfo"><identifierValue>

and after string </identifierValue><identifierType><label>Service tag

and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies

View 9 Replies View Related

How To Extract Part Of A String In Column Results

Jul 21, 2015

I want to extract a number within a column results, that is the number between the first two commas

Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....

How to extract the second number(between the first two commas) from the above results?

The output should be

1158286
1158289

View 2 Replies View Related

Assining String Variable To A Datetime Column

May 7, 2007

HI

can i assign a string column to a date column
string column contain date data.

UPDATE TABLE ENT SET EXPIRE_DATE = PREVIOUS_TRN_VALUE

where EXPIRE_DATE is a datetime column
PREVIOUS_TRN_VALUE is a string column having the value '2007-05-07'

thanks

View 2 Replies View Related

Problem Concatenating Column Values Into String...

Apr 5, 2006

I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View 6 Replies View Related

Search For Specific Length String In Column

Sep 14, 2007

SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike

View 5 Replies View Related

How To Parse A String Column With Comma Delimited

Jul 20, 2005

Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156

View 2 Replies View Related

Replacing A Portion Of Text String In Column

Jul 20, 2005

I need to replace a portion of a url in a column as a result ofchanging servers. Is there a SELECT/REPLACE/UPDATE combination querythat can do this. The table has close to a thousand entries and wouldbe nice if a query can be set to do this. Tried the REPLACE examplein the BOOKS ONLINE but it creates syntax error, apparently because itdoes not like the characters in the url and/or wildcards. I don't needto replace the entire url, only the portion before ".com". Thanks inanticipation of your help.Pradip Sagdeo

View 3 Replies View Related

How Do I Insert A String Value With Quotes Into A Nvarchar Column

Jul 20, 2007

I am reading data from another data source and storing it in the sqlce database. Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company). When I try to insert the values with the single quotes, it throws an exception. The code I use to insert the records is as follows:



cmd.CommandText = "INSERT sy_company " +

" (company_id, company, co_name, companyid) " +

"VALUES(" +

"'" + dtSYCompany.Rows[x]["company_id"] + "'," +

"N'" + dtSYCompany.Rows[x]["company"] + "'," +

"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +

"'" + dtSYCompany.Rows[x]["companyid"] + "')";

cmd.ExecuteNonQuery();



When the company name (co_name) has a single quote in it, I get the error. How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?



Thanks so much!

View 3 Replies View Related

Convert The String Column To Date In SSIS

Apr 7, 2008

Hello Everyone,
How can I convert the string column to date in SSIS.
Example:
I have a column which is having a value as €œ19890213€?
Basically this values is a date
Now I wanted this value to be a date value as €œ01/01/2007€?

i had try it out by doing this ways
(DT_DATE)(SUBSTRING(DATE_SEEN_SPEC,5,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,7,2) + "/" + SUBSTRING(DATE_SEEN_SPEC,1,4))

but still i'm getting the error message.

I know the this can be achived through type cast in Derived Column compunent, but not sure with which cast please help me out in resoulving this issue.

Thank you

View 1 Replies View Related







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