Best Way To Do Large String Manipulation - URGENT !!!
Oct 24, 2001
Hi,
I am building a string (basically XML) on the fly in stored procedure (SQL 7.0). I am using one local variable @str_xml varchar(8000) to build this string.
Now my problem is I can store maximum upto 8000 characters in "varchar type". And I can't use text field as SQL Server doesn't allow "text type" to be used as local variable.
I am thinking to use 2 or 3 local varchar variables and then return them separately to front end and will do contatination at the front end. But I think it would be ugly way to do as I have to check every time in stored procedure the length of string.
Any suggestions to do this in some other way would be greatly helpful to me.
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.
I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:
CANADA - Toronto United States- ARIZONA France Argentina United States (USA)- ARIZONA ........ ........
The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:
Server: Msg 536, Level 16, State 4, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated.
QUERY ----- select
Left( Destinations.[Destination Name], charindex("-", Destinations.[Destination Name])-1 ) as test into temp2 from destinations
CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).
I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
How i m Manipulate String , I have (ABC,XYZ,EFG) this string know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get
Hi I have a field which is a file path like 'C:avde8393948.txt'
I want to separate them into folder and filename now the filename is always the same length, so i can use RIGHT to get the filename, but i prefer a method that from the right detects the 1st occurance of and everything after is the filename,
We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid
I am having problem in string manipulation in SSIS - Derived Column Transformation. I am trying to extract the OU names from Active Directory objects into a SQL table.
Assume that a distinguish name (DN) of an object as below: CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com
How can I manipulate the above string so that I get: ParentOU/SubOU/Users
I have a sql query in which I need to isolate part of the columm valueand return only that isolated portion. I can only do this within theselect statement, and cannot add a function or anything like that. Iwould also like to keep this query within sql (I don't want to do thisin my programming environment)The string value would normally look like "segment1-segment2-segment3".I need to isolate segment2, but I have to be able to account forsituations in which either one or both dashes are missing (in whichcase returning "" or the whole string is OK. The best I have been ableto do reliably is to get "segment2-segment3".Anybody want to take a stab?
I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using: dim strText = MyTextBox.Textdim intLength = strText.Length but having problems starting here...... MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text .............. but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength) Help appreciated. Frank
I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.
For Example: "GOLF IS FUN,BOWLING IS GREAT"
What Id like to get as my results:
"Golf is fun, Bowling is great"
Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
Please help me with the sql script to manipulate the string data:
I need to add <Text> at the beginning and end of the string. I also need to add <option> before the first and after last occurence of the <Option> string. The original string
FROMTable1 INNER JOIN Table2 ON Table1.UID = Table2.UID
WHERE(SET @Temp = Table2.Column1
--remove all 0's SET @k = patindex('%[^0 ]%', @Temp) WHILE @k> 0 BEGIN SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '') SET @k= patindex('%[^0 ]%', @Temp) END SELECT @Temp ) = ''
But of course this isn't working so much. I am wondering if I have to use a cursor?
I have data in a trace file, and I need to extract some info such as phone number.The problem is the phone number could be varying lengths, and various positions in the row.
For example:
@City='New York', @Phone='2035551212' (10 characters, no dashes) or @City='San Francisco', @Phone='918-555-1212' (12 characters, with dashes) or @City+'Berlin', @Phone='55-123456-7890' (14 characters, with dashes)
I can use CHARINDEX to search & find @Phone=' so I know where the phone number starts, but stuck on a programatic way to find the data between the quotes since it can vary.
I'm used to DTS but new to SSIS. What's a good reference/tutorial that deals with transforming columns of data (from a flat file) from one format to another when uploading into SQL2005? Typically columns of data have "" around the values and spaces that I want to remove.
Presumably in SSIS I need the following:
A Data flow task containing:
Flat file source Derived or Copy Column? OLE DB Destination
I am having a field 'Flight Route that holds hyphen delimited character sequences.
E.g. : ABC-BCD-DEF-EFG.
My requirement is like this:
If the flight route is:
ABC-BCD-BCD-DEF make it ABC-BCD-DEF ABC-ABC-BCD-DEF make it ABC-BCD-DEF
i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.
The recipe preparation instructions are stored in a table by RecipeID. Â The prep instructions are in a single VARCHAR(MAX) column and look something like this: Â
1.  Boil Water 2. Add noodles 3. Add cheese sauce 4. Stir well
Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row. Â How would I accomplish this in T-SQL?
I have a web site that allows user to enter large strings into a database (comments, etc). What is the best way to do that? Right now I have them limited to 25 characters and the data type is varchar. Is there a better way?
I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.
Is there any other way to pass the large string in the drill down?
Development - Visual Studio 2005 Database - MS SQL 2005
I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.
This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.
So... Does anyone here can help me out? Thanks in advance.
Hi every one, I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value. can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
