Split The String Into Columns
I have a table called products with the values like
ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J
I need to display each productid's with
ProductId ProductName
10 A
20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J
I will be appreciated if you can send me the code.
Thanks,
Mears
View Complete Forum Thread with Replies
Related Forum Messages:
Split A Column Into 2 Columns
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
View Replies !
Split Address Value Into Two Columns
I am trying to get an address field into 2 colums. I need the number value in one column and street name in another column. The data is stored: 876 blue ct 9987 red dr 23 windyknoll This is what I haveelect substring(Address,0,charindex('',Address)) as number ,substring(Address, (charindex('',Address)+1) ,len(Address)) as address from contact
View Replies !
Split The Data Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
How To Split A String Using Sql
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend. Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns. Thanks
View Replies !
Split A String
Hi I have this string which might have a hyphen in it "-" What I want to do is if I get a hyphen then take all characters after hyphen else take only all the characters starting from the 5th position of the string How can this be achieved?
View Replies !
Split A String In Sql
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it select a name for each value and return a recordset. Any pointers greatfully received. First attempt is dreadfully slow as I am opening recordsets each time Function func_getFood() Dim rsfoodsql Dim foodoutput for x=1 to ubound(masterfoodarray)-2 set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'") if not rsfoodsql.eof then foodoutput=rsfoodsql("foodname") if not foodoutput="" then response.write foodoutput&"<BR>" end if end if next End Function Hope someone can help, cheers
View Replies !
How To Split The String
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query any one know help me Regards, Prabu R
View Replies !
Split One Column Into Multiple Columns
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.
View Replies !
Query Split Column In 2 Columns In SQL
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.
View Replies !
Split Row's Columns Across Multiple Tables
Hello, Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row? As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables. How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row. Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts. Thoughts? Thanks in advance, Dan
View Replies !
Break /split From A Cell Into Columns
hi, i have labels for data stored in one cell eg: item1; item22; item231; and i want to convert it in following output (probably using substring and charindex) No_question| item_position | label 1|1| item1 1|2| item22 1|3| item231 any idea?
View Replies !
How To Split A Delimeted String...
hi, i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines", the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less) now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter. e.g @item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10" if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null. any response will be appreciated. thanks.
View Replies !
SQL String Split With | Pipe
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont. My code below: Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''") Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()
View Replies !
Sql Charindex Split String
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View Replies !
Split String Using Delimiter
Hi, I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2' I need to split the above string and get each of those values... ' / ' delimiter Can some one please help on this. Thanks
View Replies !
Split Command In String
hi all, i am still a newbie in sql i got a problem with my query for example: ------------------------------- DECLARE @LongString VARCHAR(10) @LongString = "Hello-Word" ------------------------ i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql Thanks a lot guys arifliminto86
View Replies !
Split A Single Column Data In To 2 Columns
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 FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
MS Access Detail Split Into Columns On Report
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help. Thank you all! Timpy
View Replies !
SPLIT A String Inside SSIS/T-SQL Code?
I have a RPC which gives me multiple - single record rows like Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column Now I need to split the above record into 6 Fields and populate in my local DB I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's Any ideas / directions?
View Replies !
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Hello All, I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database. There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example: 2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1 The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed. How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table? In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND(). Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion. Thanks a bunch in advance, Chutikorn
View Replies !
Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)
Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows as an example this is what i want if you can decipher that 2005 2006 Growth 2005 Year to Date 2006 Year to Date Year to Date Growth turnover gross profit turnover gross profit turnover gross profit turnover gross profit Jan 250500 75300 280200 84100 11.85629 11.686587 250500 75300 Feb 205000 67950 190350 59900 -7.14634 -11.84695 455500 143250 take the month above and add the current months values Mar 217670 70540 234200 78000 7.594064 10.57556 673170 213790 Apr 270780 84000 290400 93000 7.245735 10.714286 943950 297790 May 265000 79260 289050 90200 9.075472 13.802675 1208950 377050 Jun 277300 81050 277900 82000 0.216372 1.172116 1486250 458100 Jul Aug Sep Oct Nov Dec Here is my Query: SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH] but it returns everything under one another 2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93 2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26 2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68 2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36 2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14 2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81 2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08 2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84 2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74 2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60 2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43 2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45 2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64 2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39 2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52 2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21 2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29 2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44 2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03 2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35 2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22 2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19 2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02 If i do get them split then it put every year's value on a different line 2005 1234123.34 32432432.43 NULL NULL 2006 NULL NULL 12312.212 15235453.21 Please Help,
View Replies !
Splitting Up One String Into Three Columns
I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction? Thanks.
View Replies !
I Want Ot Put Together A String From Multiple Columns
Here is want I did. SELECT dong2 + ' ' + dong3 + ' ' + dong4 + bunji AS 'Address' FROM zipcode.dbo.zipcode2 I want to combine all of the columns to 1 column, but some columns are empty and some are NULL. The ones that are NULL return as NULL instead of an address. How do I put this together into 1 string?
View Replies !
Parse Long String To Columns
I have a column with varchar(2000) that contains events with time-stamps. The data looks something like this for a record: 03:14:46: abc 03:14:47: def 03:14:59: xyz 03:15:17: zzz I would like to parse out each time-stamp and event to a separate columns, like col1= 03:14:46: abc col2= 03:14:47: def col3= 03:14:59: xyz col4= 03:15:17: zzz The number of events are dynamic so number of time-stamps with events can be anything from 2-30 of them. Anyone would suggest how I can resolve this? Thanks.
View Replies !
String Match Number Of Different Columns
Hi, In short: I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s) Problem: I got a set of rows with varchar(50), spread out over multiple tables. All those tables relate to a central Colour table. For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id E.g: input: 'BLACK', 'MERCEDES', '1984' Would return colour ids "025864", 45987632", "65489" and "63249" Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'. Current Situation: I) Create a table containing all possible values CREATE TABLE dbo.CommonSearch( id int IDENTITY (1, 1) NOT NULL, clr_id int, keyWord varchar(40), fieldType varchar(25) And fill it with all the values (671694 rows) ) II) Stored Procedure to cut a string up into a table: CREATE FUNCTION dbo.SplitString ( @param varchar(50), @splitChar char = '' ) RETURNS @T TABLE (keyWord varchar(50)) AS BEGIN WHILE LEN( @param ) > 0 BEGIN declare @val varchar(50) IF CHARINDEX( @splitChar, @param ) > 0 SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) , @param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) ) ELSE SELECT @val = @param, @param = SPACE(0) INSERT INTO @T values (@val) END RETURN END III)Stored Procedure to query the first table with the second one CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings] @keyWords varchar(255) = '' AS BEGIN SET NOCOUNT ON; select clr_id, keyWord, fieldType from dbo.commonSearch where keyWord in (select * from splitString(@keyWords, '')) END So, how can I use a LIKE statement in the IN statement of the last query. Furthermore, I was wondering if this is the best sollution to go for. Are there any better methods? Got any tuning tips to squeeze out an extra second?
View Replies !
Splitting Text String Into Multiple Columns
I'm creating a web-based NT RAS report site and am looking for the most efficient way to import the data from NT Event log into SQL2k. I'm using the 'dumpel' utility from rsc kit and all is fine except the 10th column - the message detail: "The user DOMAINuserid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300." I need to parse this one long text string into 6 distinct columns: userID, port, duration, bytes_xmt, bytes_rcv and portspeed. After a quick review of the rowsets, the strings seem to hold a consistent output ... no real variances I can see. I've dablled with views but am facing a small performance issue that could get bigger: The sql server not only has to run the text file import package, but also the view to format the text dump into a workable dataset, then my report code bangs over 30 queries against the final dataset. It already takes our SQL2k server over 3 minutes to parse about 20,000 rows and the server's a beast (dual 1.8 p4 cpu, 3gb ram, raid, etc). What I think would work best is to abandon the view (performance will only get worse as the row count increases) and instead INSERT the rows into one table. Any ideas anyone? any good scripts out there that can help me to parse the long text string quicker that using substring and replace functions? TIA:rolleyes: :rolleyes:
View Replies !
INSERT INTO Command (two Columns) With One Fixed Value, One String With Several Values
I have a string with values value1,value2, value3, value(n) which I would like to append to my table. In the second column of my table I have a parameter which stays the same, so I end up with (if the parameter value is "123456") Column 1 | Column 2 123456 | Value1 123456 | Value2 123456 | Value3 I would like to set up a single SQL statement which will process this regardless of the number of values (therefore rows) desired. Something like: INSERT INTO dbo_tblUserLevelApplicationRequests ( Column1, Column2) select EmployeeNumberParam as EmployeeNumber, ((stringofvalues) as valuestring) Is it possible to do this with a single SQL statement?
View Replies !
Need To Combine String Data From Multiple Columns Into One Column
When quering a table with given criteria, For ex: select notes, jobid, caller from contact where status in (6) and jobid = 173 I am getting this: This job will be posted to Monster for 2 weeks. 173 906 Waiting for full budget approval 173 906 TUrns out we're uppin 173 906 What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon? Please HELP!!!!!
View Replies !
Script To Search For A String In All Varchar Columns In All Tables In A Database?
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View Replies !
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Hello: I am running into an issue with RS2k PDF export. Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span . User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width. We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect. Any help or suggestion on this issue would be appreciated
View Replies !
Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?
Hello, i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code: Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO""" Dim oConn As New OleDb.OleDbConnection(sConn) Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn) Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader Do While rdr.Read() Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString Next it will continue to read the stuff till the String-Lines are coming. when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value. So my question is how to tell OleDB that i want that column to be completly read as String/Varchar? Thanks for Reading - Pierre from Berlin [seems i got redirected into the wrong forum, please move into the correct one]
View Replies !
A Word About Meta-data, Pass Through Columns And Derived Columns
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS): Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms. This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match. Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear. Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package. Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
View Replies !
How Dose It Matter For The Non-clustered Index Key Columns And Included Columns?
Hi, all experts here, Thanks a lot for your kind attention. As I am creating the non-clustered indexes for the tables, I dont quite understand how dose it really matter to put the columns in the index key columns or put them into the included columns of the index? I am really confused about that and I am looking forward to hearing from you and thank you very much again for your advices and help. With best regards, Yours sincerely,
View Replies !
Hiding/Showing Columns Based On The Columns Present In The Dataset
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report. Can I do that?? Any reply showing me the right way is appricited. -Thanks, Digs
View Replies !
Matching A View's Columns To It's Underlying Table's Columns
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View Replies !
Identity Columns And Date Columns On Transactional Replication
Hi, I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users. How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())? It is very important for me to have a mirror image of data (without using clustering servers). Any help would be appreciated. Thanks, -Niraj
View Replies !
SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where: Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1 Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2 Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases: Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance" Client = "Client A", Contract = "2007-2008 Maint", Project = all Client = "Client A", Contract = allI am totally stuck on: How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL) Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services. Thanks for all your help!
View Replies !
String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
I am trying to insert a row into a table of Microsoft SQL Server 2000. There are various columns. [SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:- StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters? Can anyone please tell me what's wrong?
View Replies !
Name Split
Quick question. I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it). Thanks in advance. Ray
View Replies !
Name Split
I need to split a column of Full Names into First name and Last name columns. Has someone come across this before and if so can you give me an idea of how to overcome it?
View Replies !
Split Value In Sql
Hi to all I have one problem regarding sp and pass value in sp I am gating a value like Abc,Def,Ghi, Now I want to split the whole pass value by “,� And fire one for loop to store value in database This things is done in asp.net web form but I want to do all process in sp So please guide me how I am write sp . The purpose is pass value one time so connection time is decrees and give fast perforce
View Replies !
To Split Or Not To Split
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!! Is 8,000 or so rows of data "large" and unwieldly in SQL terms? Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about............. What did they say about a little bit of knowledge being a dangerous thing? Thanks in advance of any guidance to a neophyte!!?
View Replies !
SPLIT() UDF
SQL UDF split() The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string. Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure. To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results. If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters. Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table. Here is how I handled the above situation. 1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max)) 2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string 3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data The basic structure of the stored procedure is as pasted below:- CREATE PROCEDURE FETCH_SALES_DETAIL ( @MONTH VARCHAR(MAX) ) AS BEGIN DECLARE @MONTH_CNT INT,@MTH DATETIME SET @MONTH_CNT=1 WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> '' BEGIN SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME) --<<Application specific T-SQLs>>-- (BEGIN) SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE] FROM SALES (NLOCK) WHERE [SALES_MONTH]= @MTH --<<Application specific T-SQLs>>--(END) SET @MONTH_CNT=@MONTH_CNT+1 END END Dbo.SPLIT() function takes 3 parameters 1)The main string with the values to be split 2)The delimiter 3)The Nth occurrence of the string to be returned The functionality of the UDF is as explained STEP by STEP: 1.Function Declaration CREATE FUNCTION [dbo].[SPLIT] ( @nstring VARCHAR(MAX), @deliminator nvarchar(10), @index int ) RETURNS VARCHAR(MAX) Function is declared with 3 input parameters:- @nstring of type VARCHAR(MAX) will hold the main string to be split @deliminator of type NVARCHAR(10) will hold the delimiter @index of type INT will hold the index of the string to be returned 2.Variable Declaration DECLARE @position int DECLARE @ustr VARCHAR(MAX) DECLARE @pcnt int Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter. 3.Variable initialization SET @position = 1 SET @pcnt = 1 SELECT @ustr = '' Initialize the variables 4.Main functionality WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index BEGIN IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN IF @pcnt = @index BEGIN SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar) END SET @position = @position + 1 END ELSE BEGIN SET @position = @position + 1 SET @pcnt = @pcnt + 1 END END 4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter. 4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter 4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented. 4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable 5.Return the value RETURN @ustr I hope this article would benefit those who are looking for a handy function to deal with Strings. Feel free to send your feedback at dearhari@gmail.com
View Replies !
|
TRACKER
