Comment
-----------------------------------------------------------------------
User: Toto Password: Toto-Toto
I'd like to have in the same table:
Comment User Password
--------------------------------------------------------------------------------------------------------
User: Toto Password: Toto-Toto Toto Toto-Toto
Do you have an idea of how to do it in SSIS?
Thanks a lot for your help.
I have column that hold datetime , i want to split the column into many columns ex: column --> 01/01/2007 00:00:00 i want tp split to day month year hour minute second
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query
Database : sql server tabel name :job_allocations column naME :technicain code
Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician.
Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03
user can choose any number of techincian for a job
MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
We've got a source file which owns three different values: 'A','B','M'.
Where 'A' stands for "New Rows" and 'B' for "Delete rows" and 'M' for 'Update rows'
Using Conditional Split task we can redirect each subset into a OLEDB Destination but we are wondering how can we do the same using only one OLEDB? We've got only one table.
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)
Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469
How can i acheive this one with out data lose in Address Column. Thanks in advance.
I found string from net how to split column data to row
SELECT A.JbIDFull, A.ProdID, A.OrderQty, A.OtherDetails, A.OrderDate, c.Item FROM (SELECT JbIDFull, DeptID, ProdID, OrderQty, OtherDetails, OrderDate, LamORSteachType, JBID, RMIDs, RMQty FROM tblJobCardforProduction WHERE JBID = '2' AND DeptID = '3') A CROSS APPLY dbo.SplitStringNEW(a.RMIDs,';') b CROSS APPLY dbo.SplitStringNEW(a.RMQty,'/') c
When I apply one cross apply it's working fine but when i apply for one more column is replacing three time one row, this data i want to split RMIDs and RMQty with same JbIDFull
JbIDFull  DeptID   ProdID OrderQty    RMIDs RMQty
PD-May15-00001 3 2044 10000Â Â Â Â PROD-00052 0 PD-May15-00002 3 921 1000 PROD-00052;PROD-00383;PROD-00384 500;600;700
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , InvNoVARCHAR(10) NOT NULL , SalesCode NCHAR(80) NOT NULL
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive
I have a column with the following information in it. I need to split the column and parse the values. I have Parsed one single values from but, not multiple times.
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , InvNoVARCHAR(10) NOT NULL , SalesCode NCHAR(80) NOT NULL , Amount NCHAR(80) NOT NULL
I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below .
1 ,AU-Australia 1,MM-Myanmar 1,NZ-New Zealand 1,PG-Paua New Guinea 1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country, Split.a.value('.', 'VARCHAR(500)') AS String FROM (SELECT id, country , CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
I'm sure there is probably a very easy solution that I am just not seeing or can't Google...
I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?
I'm trying to write a conditional split where I want to bring in only records where the date is less than today, but my problem is that I can't simply do this Column < GetDate() because if something comes in today, it takes the time into account and it will bring that record for today. You can do this in SQL, but I'm not sure how to do that in SSIS
I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.
Old Table:
Code: ID, StringValue
New Table:
Code: ID, Value1, Value2 Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.
Code: SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1) WHEN 0 THEN OldTable.stringvalue ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1) END AS FirstWord FROM OldTable
Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?
Code: @declare firstDigit int IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1 set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int) ELSE set @firstDigit = -1
I am trying to join two tables and looks like the data is messed up. I want to split the rows into columns as there is more than one value in the row. But somehow I don't see a pattern in here to split the rows.
This how the data is
Create Table #Sample (Numbers Varchar(MAX)) Insert INTO #Sample Values('1000') Insert INTO #Sample Values ('1024 AND 1025') Insert INTO #Sample Values ('109 ,110,111') Insert INTO #Sample Values ('Old # 1033 replaced with new Invoice # 1544') Insert INTO #Sample Values ('1355 Cancelled and Invoice 1922 added') Select * from #Sample
This is what is expected...
Create Table #Result (Numbers Varchar(MAX)) Insert INTO #Result Values('1000') Insert INTO #Result Values ('1024') Insert INTO #Result Values ('1025') Insert INTO #Result Values ('109') Insert INTO #Result Values ('110')
[Code] ....
How I can implement this ? I believe if there are any numbers I need to split into two columns .
