Transact SQL :: Extracting A Word From String?

Jun 30, 2015

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.

View 6 Replies


ADVERTISEMENT

Transact SQL :: Find Exact Word In String Within Case Statement

Aug 6, 2015

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.

View 10 Replies View Related

Extracting Out The Rdl (layout Info) From Given Pdf/word Document

Dec 20, 2007



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.

View 3 Replies View Related

Extracting Part Of A String

Aug 22, 2006

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!

View 5 Replies View Related

Transact SQL :: Extracting Certain Text After Special Character

May 18, 2015

I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt

View 9 Replies View Related

SQL Server 2012 :: Extracting String Between Certain Characters

Aug 18, 2014

I 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.

View 5 Replies View Related

SQL Server 2008 :: Extracting Numbers From String

Jun 2, 2015

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.

View 9 Replies View Related

Extracting A Portion Of A String In SQL Server 2005

Apr 22, 2008

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

View 10 Replies View Related

Transact SQL :: Extracting From 3 Related Tables And Including MAX Function

Oct 5, 2015

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).

View 7 Replies View Related

Transact SQL :: Extracting Specific Data From One Field Using Charindex

Jul 20, 2015

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?

View 4 Replies View Related

How To Read Third Word From A String?

Feb 6, 2007

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

View 12 Replies View Related

Word Search In String

Feb 25, 2008



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


View 7 Replies View Related

Transact SQL :: To Get FirstName And LastName From One Word

Aug 25, 2015

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?

View 7 Replies View Related

How To Extract Random Word In A String

Aug 29, 2013

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.

View 1 Replies View Related

Extract Specific Word From String

Oct 15, 2015

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.

View 4 Replies View Related

Transact-SQL: Word Sample Reserved In SQL2000??

Sep 26, 2007



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

View 6 Replies View Related

Transact SQL :: Script To Return All Rows With Word Hello

Jul 30, 2015

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...

View 29 Replies View Related

How To Check If The Word Is In String (Case Sensitive)

Aug 16, 2013

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.

View 4 Replies View Related

Query Word Starting With....from String Caught In Web App

Jul 23, 2005

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 Related

Query For Taking Each Word In A String, And Putting A Comma After It?

Aug 29, 2007

Hey 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 Related

Script Component Task: Function That Returns Last Word In A String

Sep 7, 2006

I 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

 

View 3 Replies View Related

The String Was Not Recognized As A Valid DateTime. There Is A Unknown Word Starting At Index 0.

May 2, 2008

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

View 3 Replies View Related

System.FormatException: The String Was Not Recognized As A Valid DateTime. There Is A Unknown Word Starting At Index 0.

Sep 26, 2004

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

View 4 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

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

View 3 Replies View Related

Transact SQL :: How To Get String Array In String Variable

Jul 28, 2015

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.

View 8 Replies View Related

Can I Change Null Word In Report To Another Word

Feb 24, 2008

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 Related

Sql Search Command Word By Word?

Mar 30, 2005

hi!
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.

View 4 Replies View Related

Transact SQL :: Want To Spilt String

Sep 9, 2015

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.

View 8 Replies View Related

Transact SQL :: String Split After The Space

May 18, 2015

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 

View 6 Replies View Related

Transact SQL :: Concatenate String Using Conditions?

Sep 19, 2015

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 ''

View 5 Replies View Related

Transact SQL :: Search For String In Table

Apr 29, 2015

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.

View 10 Replies View Related

Transact SQL :: String Hex Comparison And Partitioning?

Oct 22, 2015

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?

View 4 Replies View Related

Transact SQL :: Error On String Comparisons

Jul 7, 2015

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

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved