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


ADVERTISEMENT

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

When I Use SSIS For Extract From OLAP Database, Then The Error Random Occurred,Error Code = 0x80040E05

May 10, 2007



I have tired for this!

When I use SSIS for extract data from ssas, that means,I use mdx query.

then random error occured.



Hope some one can understand my poor English....



And the Error Info show below.






Code Snippet

Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".
Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.

View 6 Replies View Related

Random String

Apr 15, 2002

I used this to generate a random password. You can change what it returns to up to an 8000 character password if you feel brave. You will need this function (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14924) to generate the random numbers. For some reason, occassionaly a NULL (CHAR(0)) character would get in there and mess things up so I had to remove those. You could change this to even include the non alpha numeric characters.

create procedure random_password
@length smallint,
@password nvarchar(256) = null output
as
set nocount on

declare @number int
declare @lower int
declare @upper int
declare @part tinyint

if @length > 256 set @length = 256
set @password = ''

while len(@password) < @length
begin
exec random_number 0, 3, @part output

if @part = 0
select @lower = ascii('a'),
@upper = ascii('z')
else if @part = 1
select @lower = ascii('A'),
@upper = ascii('Z')
else if @part = 2
select @lower = ascii('0'),
@upper = ascii('9')

set @part = null
set @number = null
exec random_number @lower, @upper, @number output
set @password = @password + ltrim(char(@number))
if substring(@password, len(@password), 1) = char(0) set @password = left(@password, len(@password) - 1)
end
go

View 3 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

Extract INT From String?

Feb 19, 2014

I have a table which has important information stored within a text field as nText. The fields look like this

{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}

The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?

I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.

,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)

I use Microsoft SQL 2008

View 6 Replies View Related

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

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

String Extract From Text

Jun 17, 2014

I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.

Begin Quote Calculation<br />
<br />....<br />
Agent Id: 001<br />
Trip Duration: 5days<br />
Relationship Type: Individual<br />....nDestination: AreaTwo<br />
<br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 9.070000<br />.....

Result

Trip Duration: 5 days
Destination: AreaTwo
Base Rate: 6.070000

View 3 Replies View Related

How To Extract Part Of A String

Jul 23, 2005

Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.

View 4 Replies View Related

How To Extract Part Of String

Aug 22, 2007

Hi,


I have 2 questions.

1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
The length of "Name" varies and I want to extract all characters included for "Name".
Can anyone advise what the query/SQL statement would look like?



2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.
So I want to include a code that checks if file "file.xls" is open on user's machine.
If file is open, then message "file "File.xls" is already open. Generating File_1.xls"
Run the code but create the file with file name "file_1.xls"
If file doesn't exist, then run code and create file with file name "File.xls"

Please advise.

View 8 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

Search String And Extract To Another Table

May 4, 1999

Hi,

Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?

Example:

Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF

I want to extract the postcode from this field into another field in another table....

Example 2:

Name
-----------
Ivor Smith

I wish to strip the last name from the field, and put this into a field in another table..

David

View 10 Replies View Related

Extract A String In A Stored Procedure

Jul 20, 2005

Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq

View 4 Replies View Related

Transact SQL :: Extract String With Delimiter

Nov 2, 2015

I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.

View 4 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

SQL Server 2008 :: How To Extract Part Of A String

May 11, 2015

Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934

I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.

My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.

So an example would be the following:

OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954

SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]

How can I do this?

Few notes:

-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.

View 2 Replies View Related

How To Extract Part Of A String In Column Results

Jul 21, 2015

I want to extract a number within a column results, that is the number between the first two commas

Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....

How to extract the second number(between the first two commas) from the above results?

The output should be

1158286
1158289

View 2 Replies View Related

Extract Numbers Or Letters From Mixed String

Mar 16, 2004

Yeah, it's pretty simple. Maybe it'll help someone out.


-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''

IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'

WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END

View 4 Replies View Related

Extract Substring From String(Regular Expressions)

Feb 11, 2008

Hello forum, I need extract a substring form a string that follows a regular expressions -->

T:1º/PQ:1/TALLA:2(MOD.51 100/150)

T:<number>º/PQ:<number>...

I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...

I would like me use it as:
SELECT RegularExpression (Field)

Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.

View 3 Replies View Related

Extract Data From Middle Of String In SQL Server

Jan 11, 2008

I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:

DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_

I want to select the 8 characters after the first comma in the field, and then convert it to a date format.

I am a novice, so any help with the correct function and syntax would be greatly appreciated.

thanks,
Dave

View 8 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

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

T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String

Jul 9, 2014

I have a column that contains the follwoing string I need to compare.

ek/df/cv/
ek/df/cv/f

All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.

I can't do it by using the combination of Substring() and charindex() and Len()

View 9 Replies View Related

T-SQL (SS2K8) :: Extract String - Variable Sizes With Breaks?

Jun 18, 2015

Row Value :

Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun

and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.

Should I just go for charindex on break() or is there a another way ?

View 3 Replies View Related

Extract IP Address From Command String - Subscript And Charindex

Apr 21, 2014

I am trying to extract the IP address from a command string, the problem I am running into is there are 3 types of command strings.

SENDAUTO IP192.168.1.32L0O11 Z1 5(E=00:00,F=00:00,G=00:00,H=00:00,I=00:00,J=00:00)

SENDCREATEEXCEPTION -1,IP192.168.1.32,0,11,0,Z1,Free text-label,19,3,19,3,06:00|24:00,I|O,1,288003,1

SENDWEEKTIMES IP192.168.1.32,0,11,Z1,3,100,23:00|24:00|24:00|24:00|24:00|24:00,I|O|O|O|O|O

The IP address length can vary, and the character after the IP is either a L or ,

How can I edit the following function to look for both characters?

SUBSTRING(Command, CHARINDEX('IP', Command), CHARINDEX('L', Command) - (CHARINDEX('IP', Command)))

View 6 Replies View Related

Transact SQL :: String Function To Extract Matching Rows

Oct 6, 2015

Consider the following: I have a table, say ORDERS, with these entries -

CustID
ProductID
1       CAN
2       2
3       1,2
4       4
5       1,2,3,4,5,CAN
6       10
7       CAN
8       1,CAN

I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.

View 12 Replies View Related

Transact SQL :: Extract ID And Specific Part Of A String From Column

Jun 10, 2015

I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.

I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)

The row sample looks like the following

I have to extract the Id and a part of the column from mystring.

Id      MyString
1      ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|

[Code] ....

The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following

Id      DesiredString
1      Extracts^This^String1
2      Extracts^This^String2
3      Extracts^This^String3

Is there a way to extract this either using TSQL or SSIS.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,

[Code] ....

View 4 Replies View Related

Extract Specific Words From A Free Format String

Feb 27, 2008

I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.

My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.

The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.

So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.

I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.

I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.

View 4 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

Reporting Services :: How To Extract Only Numeric Values From A String In SSRS

May 29, 2015

I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these  multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.

=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",   ",","),",")
The value will look like this
01-Manger
02-Senior Mange
21-Associate
25-Associate Trainee

This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()

=SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",  
",","),1,2),",")

Result will be   01

But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.

View 5 Replies View Related







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