Splitting String In To Keywords
Dec 6, 2007Hi
I have a string EX: "How are you doing" Now. I wanted this string to be splitted in to respective words and return me those words in sql server. .
Thanks!
ADVERTISEMENT
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Sep 11, 2006Hello 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
Splitting A Sql String
Oct 28, 2004aaaaa,bbbb,ccccc,dddd,ffff,gggg,llll,kkkk,nnnnn
How can split the above string to the following individual strings
aaaaa
bbbb
ccccc
dddd
ffff
gggg
llll
kkkk
nnnnn
Thanks
Splitting A String
Nov 30, 2005Hello,I have been placed in charge of migrating an old access based databaseover to sql server 7.0. So far, I have imported all the tables intosql server, but now I have come across the issue of needing to split astring variable. For instance, in the old database, the variable forname was such that it included both first and last names, whereas inthe new database there are seperate entities for first and last name.I know that there is a way to write a script that will separate out thetwo strings by using the "space" in between the name, but I'munfamiliar how to do this. Any suggestions? Thanks!Rick
View 1 Replies View RelatedSplitting Up A String
Jan 9, 2008If I have a string of
'WLL EXT FACE REND'
how would I go about splitting the string into 4 strings of
'WLL'
'EXT'
'FACE'
'REND'
I know that for the first bit I can use a combination of PATINDEX and LEFT, but not sure how I can pull out the rest of them.
Thanks
Splitting FullName In A SQL String..
May 28, 2004I use to use the Instr() method, but that is not allowed in SQL..
Any suggestions?
Splitting Up One String Into Three Columns
Aug 14, 2003I'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.
Splitting A String By The '|' Character
Aug 22, 2007I need to creat distinct terms of the example parsing the term on the '|' character. I will be using mysql.
example: 1885-1974.|Johnson family|Frontier and pioneer life - Alberta - Black Hill district|Cadogan region (Alta.) - Biography|Black Hill district (Alta.) - Biography
Splitting Alphabets And Numbers In A String
Mar 2, 2012I have a column in a table which looks like below.
Column
-------
AA123
D123
AXC1
QF23
I need to split this value into two part, Alphabets and numeric. How to do this using SQL query.My column value will not have mixed characters like A1D3,G32S,12F.It will always follow the ablve pattern mentioned above.
Splitting String Of 2 Columns In Same Table
Sep 25, 2014I have a table name tbl_testme with columns (id,mac,keys,outputmk)
mac column have 12 character and keys have 16 character
mac keys
6545da7n9hg8 hsi457s5sd77jk87
What i want is i need to split the column into 4 characters of both column E.G.
(6545 da7n 9hg8) and (hsi4 57s5 sd77 jk87)
after splitting i need to take last 4 character of key(jk87) and last 4 character of mac(9hg8)and join them and insert that into ouputmk column.
E.G.
(jk87-9hg8-sd77-da7n-57s5-6545-hsi4)
I need this to insert in outputmk column ....
Splitting A Comma Delimited String
Oct 1, 2007I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be
3:4:5:4
2:4:1
4:1:2:5:2
I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows:
Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)
Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2'
While @loc != 0
begin
set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
set @loc = charindex(',',@person,@loc+1)
Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
print 'String = ' + @Str
set @cnt=@cnt+1
end
RESULT
------
String = 3:4:5:4
String = 2:4:1
String = 4:1:2:5:2
Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.
Splitting Text String Into Multiple Columns
Dec 19, 2002I'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:
Splitting Space Delimited String Inline
May 20, 2008So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).
Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?
SELECT forenames
, Replace(forenames, ' ', '.')
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
FROM ( SELECT 'John' As [forenames]
UNION SELECT 'John Paul'
UNION SELECT 'John Paul George'
UNION SELECT 'John Paul George Ringo'
) As [x]
Results
forenames (no column name) f1 f2 f3 f4
---------------------- ---------------------- ---- ---- ------ -----
John John John NULLNULL NULL
John Paul John.Paul John PaulNULL NULL
John Paul George John.Paul.George John PaulGeorgeNULL
John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo
SQL 2012 :: String Splitting Using Like And Data Cleansing
Mar 28, 2014I have a large poorly designed table (inherited) With a Name field that contains comma delimited text containing address information. I need to do several things with it but unfortunately there doesn't appear to be any true consistency in it. When it displays in its own text box it works by placing each section on a new Line and looks ok.But I need to pull it apart and place things like unit number, Building Name in its own column etc. In the data it could be in either the 2nd,3rd, 4th, dependent on what came 1st. the data looks some thing like the following
unitNumber/StreetNumber Space StreetName (Building Name), Subub,City,Country
Some addresses won't have unit number or Suburb or country so when splitting you could have Suburbs and Citys in multiple columns even if you try and stagger the split process.Has any body go a good tool or reference site for dealing for this sort of problem. I have a table that I have made up that has some of the street names that could be used for comparing against existing records but it is by no means fool proof due to spelling inconsistencies . I also have another list of Common building names that could be used to compare, remove and place in the new building column.
ORDER BY Question: Splitting String Into 2 Orders?
Jul 20, 2005I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, so that the order would be by the second set of four numbers(i.e. between the first and second - marks), followed by the third setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
View 1 Replies View RelatedHelp Required For Splitting Up String Variable Using Comma Separator
Jul 9, 2007I need a help in SQL Server 2000.
I am having a string variable in the format like -- (1,23,445,5,12)
I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.
Can anyone help me out in splitting the variable using the comma separator...
URGENT!!! Search Tool Function: Splitting A String
Jan 14, 2008Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')
Thanks for all your help in advance!!!
Opinion About Design Needed (splitting String Data)
Dec 14, 2006Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
View 7 Replies View RelatedSplitting A String By A Delimiter, But Ignoring Multiple Delimiters
Oct 22, 2007Hi,
I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings:
CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS
(
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
This works very well, other than instances of the delimter are, themselves, considered to be results. For example:
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result
returns:
pn s
1 foo
2 bar
which is exactly the result I would want.
However,
SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar'
returns
pn s
1
2 foo
3 bar
4
And
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar'
returns
pn s
1 foo
2
3
4 bar
I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course.
In other words, all three examples above should produce the same result:
pn s
1 foo
2 bar
How can I do this? Any thoughts much appreciated...
Keywords For Sql 7.0
Aug 20, 1999Where can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
New Keywords ?
Sep 21, 2007does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies View RelatedUsing Sql Keywords
Apr 25, 2008When designing a table, or column name. I am sure its best to avoid using table names or column names that use reserved words such as
"status" and "message"
What happens when we name a column or table using these words? Any tiny performance hit ? I am just wondering whats happening behind the scenes.
Thanks!
mike123
Keywords
May 16, 2008How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN
ID Keyword
1car
1boat
1van
14test
14car
14bike
4VAN
5 scooter
Several Keywords In The Same Textbox
Dec 27, 2006Hey
I got a SQL DB with three tables, where two of the collums of the main table gets its data from two other tables, ok? Now, on one page, called "search" the user should be able to search in the DB, and on another page the user should be able to register some keywords, my question is how can the user register several keywords in the same textbox, using perhaps a ; as a separator between the words? I hope you understand what I mean..
Thx!
Using Keywords As Attributes
Aug 24, 2004I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Thanks,
_z0
HOW TO SEARCH T-SQL KEYWORDS
Apr 10, 2008I WANT TO FIND THAT WORD WHICH IS START'S FROM % KEYWORD
SELECT ENAME FROM TABLENAME WHERE ENAME LIKE '%%'
HOW CAN I FIND PLS HELP ME OUT .
Yaman
Keywords Search Query
Feb 6, 2007Hi,
I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.
here is what i have so far but it always returns the same thing. any ideas?
$searchArray=explode(" ",$search);
$query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";
foreach($searchArray as $key){
$query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";
}
$query = substr($query,0,strlen($query)-3);
$query = $query . ") order by keywords";
any ideas?
SQL Keywords As Column Names
Oct 13, 2005SQL server throws an exception when I try to run the following code:
ALTER TABLE contacts ADD default ntext NULL;
The reason for that is that "default" is an SQL keyword.
The way I handle this now is I put an underscore at the front:
ALTER TABLE contacts ADD _default ntext NULL
Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?
Thank you!
Sql Keywords Deleting Records?
Jun 3, 2008I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50))
CREATE TABLE [Company] (Id int, Name varchar(50))
CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee]
AS
SELECT *
FROM [Employee] INNER JOIN [EmployeeCompany]
ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate]
ON [dbo].[dvEmployee]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE EmployeeCompany
SET Status = INSERTED.Status
FROM EmployeeCompany, INSERTED
WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId
AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId
END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
OLE DB Destination Problem - With Keywords
Aug 16, 2007Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?
View 3 Replies View RelatedUsing Replace Function With 2 Keywords To Look For
Oct 16, 2007
Hi,
We have this syntax from Microsoft for Replace function:
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
I want to search for two keywords and not only one:
images and images/ (second one is with back slash.
Is it possible to include something like:
images/ OR images in expression2 like
REPLACE ( string_expression1 ,'images/ OR images', string_expression3 )
I want something like that, how do I get that please?
Thank you.
Using Multiple Keywords To Search The Data
Mar 11, 2008I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search
(
@eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3)
)
AS
SELECT eName, ID, cID, eCC
FROM iTrns
WHERE (ID LIKE '%' + @ID + '%') and
(eName LIKE '%' + @eName + '%') and
(cID LIKE '%' + @cID + '%') and
(eCC = @eCC)
========
My code behind========protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = GetTable();
GridView1.DataBind();
}public DataSet GetTable()
{SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString);
SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text);
cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text);
cmd.Parameters.AddWithValue("@eCC", TextBox4.Text);
Adptr.SelectCommand = cmd;
DataSet ds = new DataSet();return ds;
SqlCon.Close();
}
========
Sometimes it retrieves the whole data !! and sometimes retrieve nothing !!
Any hint ??
Thank you
Q:Keywords Query In Store Procedure
Nov 6, 2003--keywords table:
CREATE TABLE #KeyWord
(
[QueryId] int IDENTITY (1, 1) NOT NULL ,
[QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND)
[SKey] nvarchar (200) NULL
)
ON [PRIMARY]
--target table:
CREATE TABLE Test
(
[PId] int IDENTITY (1, 1) NOT NULL ,
[PTitle] nvarchar (200) NULL
)
ON [PRIMARY]
================
I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
PS. sqlserver's full text index mode is off.