Transact SQL :: Extracting A Word From String?
Jun 30, 2015Is there a function / way to extract a word from a particular position in the String.
I want to extract a word which is in the 16th position. All words are separated by spaces.
Is there a function / way to extract a word from a particular position in the String.
I want to extract a word which is in the 16th position. All words are separated by spaces.
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
Hi everyone,
Is it possible to extract rdl template from a given pdf or word document? We are specifically interested in extracting layout information. We want to do it programatically.
The content and layout of pdf or word document are simple.
Hello,
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt
View 9 Replies View RelatedI need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
Anyny in-built sql function that gives us numeric values in a string?
I have to deal with some inconsistent US phone numbers stored in DB. They are stored as
(xxx)xxx-xxxx
xxx xxxx xxxx
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxxxxxxxxx
I don't want to apply nested REPLACE function to eliminate all unnecessary characters to get 10 digit number from DB.
Hello, this seems simple but I've been banging my head a while.
I have a data field that is formated like this: NYT/2000-Subways/7510-Electronics Mtc/7540-Data Svcs.
I need to pull out the string after the second / and before the third / in this case the value is 7510-Electronics Mtc but it does change.
I have this function that returns the first value (NYT):
USE [Data_Warehouse]
GO
/****** Object: UserDefinedFunction [dbo].[Get_Dept] Script Date: 04/22/2008 09:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Provide the phrase before (ex. 'Worklog Related to'), the field searched (ex. UPDATE_ACTION), and
-- the phrase after (ex. ']').
-- This function returns the string found after any phrase and before any phrase, within any field searched.
ALTER FUNCTION [dbo].[Get_Dept](@in_Phrase_Before varchar(250),@in_Search_Field varchar(250))
RETURNS varchar(250)
AS
BEGIN
Declare @str_String As varchar(250)
Declare @tmp_String as varchar(250)
Set @str_String = null -- to clear the variable buffer
Set @in_Phrase_Before=char(37)+(LTrim(Rtrim(@in_Phrase_Before)))+char(37) -- if 'Null', all strings will be returned
If Left(@in_Search_Field,4) = 'NYT/'
BEGIN
Set @tmp_String = Right(@in_Search_Field,(len(@in_Search_Field) - 4 ) )
Set @in_Search_Field = @tmp_String
END
If (PatIndex(@in_Phrase_Before, @in_Search_Field)) > 0 --Checks to see if the phrase before is in the search field
Set @str_String=SubString(@in_Search_Field,1, (PatIndex(@in_Phrase_Before, @in_Search_Field)-1) )
RETURN @str_String
END
Thanks in advance for the help
I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments
Column ID -- int, primary key
Column sInstrument -- string
Table PTRS_INSTRUMENTATION:
Column ID -- int, primary key
Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
Column lMainItem -- int, points to a row in table MAINITEMS via ID
Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
Table MAINITEMS: List of chamber music works
Column ID -- int, primary key
Column sItem -- string
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975 ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656 ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712 ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
Hallow, everyone:
I want to read third word from a string. For example, the string is,
ABCDE fghijk 031 LPN OPQ
I need 031. How to read it?
Any help will be appreciated.
Thanks
ZYT
Hi All,
Please help, it is urgent
I am having a colume as image datatype, in which candidates resume is stored in binary format.
I have to search for particular words for eg. "ASP.Net, 2 Years" into that column
I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX
DECLARE @i int, @j int
SET @i = select MIN(ResumeId) from table_name
SET @j = select MAX(ResumeId) from table_name
WHILE (@i <= @j)
BEGIN
DECLARE @Resume varchar(max)
SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))
--code to search the particular word from @Resume
END
It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records
How can I make it faster or is there any other way to search?
Thanks in advance
How to retrieve data as a two names from one columns.
For example: My column has naming like this Shannon.Cooley, I want to display like this:
FirstName LastName FullName
Shannon Cooley
Shannon Cooley
How to write the query to get the above table?
Creating a stored procedure in extracting a word in a sentence.
Example:
String=The quick brown fox JuMp over the lazy dog.
I want to extract a random word on the string.
I have a values a column like this
Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)
So i just only need 07:00-16:30 this kind of part from above all these string in sql server.
Hi,
I haven't found anything that shows that "sample" is a reserved word in SQL 2000. However when I enter it in Query Analyzer, the color changes to blue, as it is a transact-sql word. However, I'm still able to create a table called sample or name the column.
Does anybody know why query analyzer recognizes it as a transact-sql/reserverd word??
Thanks in advance,
Jorge
Is there a script that will return all rows in all tables that contain the word 'hello'?
I have (1) database named "Test" that has 129 tables in it and I just want to know which tables the word "hello" is in...
I have a scenario which i must check if the value of the inputted word is exact (case sensitive) in a string. And print the word if correct. using a user defined function.
Example:The quick brown fox jump over the lazy dog.
If the user enter "jump" it must print "jump".
If the user enter "Jump" it must print "jump" which is the correct word.
Hi allThis should be easy for one of you pros out there...I take a single string(one letter) from a textbox and want to query mydatabase for a name that starts with that letter.I know that the correct synthax when you know the letter is:WHERE FirstName LIKE 'O%'but when I have this:*****************Dim lastname As Stringlastname = txtlastname.TextDim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Namelike ' txtlastname % ' "**********************************************It doesn't seem to work since my datagrid is empty afterwards. I alsotried:***************Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Namelike '" & txtlastname & " % ' "****************without succes....It's probably just a synthax error on my part but I don'T know were..THanks guys!!JMT
View 3 Replies View RelatedHey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?)
View 1 Replies View RelatedI have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task
create a function called CleanString (ByVal CustomerName as String) As String
CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd
ie parse 'Mr John Tools' and the function returns 'Tools'
ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'
ie parse 'Microsoft Corp' return 'Microsoft Corp'
ie parse 'Digidesign Ltd' return 'Digidesign Ltd'
Any idea of a regular expression or existing piece of existing code I can have
thanks in advance
dave
Hi,
I got this error report "The string was not recognized as a valid DateTime. There is a unknown word starting at index 0." at this line of code in BOLD.
For some reasons the datetime format in the database has the (US datetime format - 05/02/2008 06:40 instead of United Kingdom (UK date time format - 02/05/2008 06:40), as I had planned. I have tried to reformat the database and VB date format and all to no success.Could this be the root of my problems?
Code Snippet
Public Class Form1
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If con.State = ConnectionState.Open Then con.Close()
con.Open()
cmd = New SqlCommand("SELECT REM_NOTE, REM_DATE FROM dbo.MY_CONTACTS", con)
Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.HasRows = True
Dim newAlert As New Alert(Reminder_data_reader("REM_NOTE").ToString(), DateTime.Parse(Reminder_data_reader("REM_DATE").ToString()))
Me.collectionOfAlerts.Add(newAlert)
End If
End While
sdr.Close()
con.Close()
End Sub
End Class
i cannot seem to get rid of this error. My page works in other functions that have the same syntax and format. just this one doesnt. i tried changing the format but still the same thing.
SQL DB is DateTime. Any Suggestions?
Function UpdateException(ByVal qID As String, ByVal exception_Status As String, ByVal completed As String, byval notes as string, byVal Username as string) As Integer
dim strDate as date = datetime.now()
dim strdate1 as string = strDate.tostring("MM-dd-yy")
Dim connectionString As String = "server='servername'; trusted_connection=true; Database='CPD_Reports'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [Master_CPD_Reports] SET [Exception_Status]=@Exception_Status, [Completed]"& _
"= @Completed, [Completion_Date]= @Completion_Date, [Username]= @Username WHERE ([Master_CPD_Reports].[QID] = @QID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@QID", System.Data.SqlDbType.VarChar).Value = qID
sqlCommand.Parameters.Add("@Exception_Status", System.Data.SqlDbType.VarChar).Value = exception_Status
sqlCommand.Parameters.Add("@Completed", System.Data.SqlDbType.VarChar).Value = completed
sqlCommand.Parameters.Add("@Completion_Date", System.Data.SqlDbType.varchar).Value = strDate1
sqlCommand.Parameters.Add("@Username", System.Data.SqlDbType.DateTime).Value = UserName
dim NotesUpdate as string = "INSERT INTO [CPD_Notes] ([qID], [Notes], [Notes_Date], [Username]) VALUES (@qID, @notes, @Date, @UserName1)"
Dim sqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(NotesUpdate, sqlConnection)
sqlcommand1.parameters.add("@qID", system.data.sqldbtype.varchar).value = qID
sqlcommand1.parameters.add("@Notes", system.data.sqldbtype.varchar).value = notes
sqlcommand1.parameters.add("@UserName1", system.data.sqldbtype.varchar).value = Username
sqlcommand1.parameters.add("@Date", system.data.sqldbtype.datetime).value = DateTime.now()
Dim rowsAffected As Integer = 0
trace.warn(strdate1)
sqlConnection.Open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
End Try
dim rowsAffected1 as integer = 0
Try
rowsAffected1 = sqlCommand1.ExecuteNonQuery
Finally
End Try
Return rowsAffected
return rowsaffected1
sqlConnection.Close
End Function
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
i have report with parameter and he can have a null in parameter ther is null word can i change it to another word like all or any thing else
View 4 Replies View Relatedhi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.
I want to split the string 'MR JAMES WELL II' into three what is the easiest way of doing?
First part = MR
Second part = II
Third part = JAMES WELL
Tried couple of methods using CHARINDEX/REVERSE but not getting through.
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) -1, -1)) AS [FirstName],
RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName))) AS [LastName]
This is only gives first and last not first and middle
DECLARE @FullName VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT
STUFF(@FullName,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+5,LEN(@FullName),'') [Firstname1],
STUFF(@FullName,1,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+4,'') Lastname1
Not right as it gives
Vauxhall
Adam Rocks AIR Vauxhall
Ideally the result should be
Vauxhall
Adam Rocks AIR
I have a SQL table like this
col1 col2 col3
1 0 0
1 0 1
1 1 1
0 1 0
I am expecting output as
col1 col2 col3 NewCol
1 0 0 SL
1 0 1 SL,PL
1 1 1 SL,EL,PL
0 1 0 EL
condition if col>0 then SL else '', if col2>0 EL else '', if col3>0 PL else ''
Currently we have requirement like below.
Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"
In the DB i have column values like below.
FileType Val
FILENAMETYPE Direct
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
Our dev team wants to introduce a complex key, which is made up of
EventDate datetime2(5) = ‘2015-10-22 10:19:59.12345’
ConsumerID bigint = 1234
SiteID tinyint = 15
EventDate will be converted to a bigint and then to a hex value
= 2015-10-22 10:19:59.12345 = 2015102210195912345 = 1BF714C8A0D4F699
ConsumerID will be converted to a hex value
= 1234 = 4D2
SiteID will be converted to a hex value
= 15 = F
The hex values will then be concatenated together and stored as a string (varchar). The application will handle the creation of the complex key
= ‘1BF714C8A0D4F699-4D2-F’
I am trying to argue against this approach and get them to store the values in their native form, in separate columns as a natural key.
To make matters worse, we need to use partitioning, where the partition boundary will be on a hexed datetime2(5) at weekly intervals. I was really hoping a proof of concept would show that hex string comparison could potentially put a row under a wrong partition, but so far, on an 8 million rowset, it is working fine.
For example, in the proof of concept, right partition boundary:
= '1BF398D53DFA1800'
(2014-12-15 00:00:00.00000)
contains rows
= '1BF3983242B9C000-1-2'
(2014-12-08 00:00:00.00000-1-2)
Through to
= '1BF398C373960580-2FAF3003-2'
(2014-12-14 23:59:00.00000-800010243-2)
The prior partition boundary is 1
= 'BF3983242B9C000'
(2014-12-08 00:00:00.00000)
How is this working, given that the string lengths (varchar) are different and the row value contains dashes and the partition boundaries are smaller in length without dashes? How varchar string comparison is working here?
I am trying to compare the ADDRESS FIELDS Between 2 tables in SQL SERVER 2008. However when I run the comparisons below it throws the error below:
Query:
select
inner
JOIN TABLE2 B
ON
COLLOAN=
COLLOAN1
a.ADDRESS<>b.PropertyAdd
Error : Cannot resolve the collation conflict between "SQL_Latin1_General_Pref_CP437_CI_AS" and "SQL_Latin1_General_CP850_BIN" in the equal to operation.
WHERE
A.Address ,b.PropertyAdd
,a.*
from TABL1 A