I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.
Things you will need to keep in mind are;
The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.
Code=web.co.uk% product_250p&Tr anID=14078800&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% product_free&Tr anID=14077576&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077583&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077584&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_150p&Tr anID=14077579&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077603&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077741&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% product_250p&Tr anID=14077757&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% 2Fpush_wallpape r_250p&TranID=1 4077770&OtherFl ag0=services.we b.stats.PostCon tentDelivery&Ot herFlag0par=del iv
Code=web.co.uk% product_250p&Tr anID=14077604&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Regards,
Ciarán
8  5765  ch********@hotm ail.com wrote: I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.
Things you will need to keep in mind are;
The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.
Code=web.co.uk% product_250p&Tr anID=14078800&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% product_free&Tr anID=14077576&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077583&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077584&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_150p&Tr anID=14077579&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077603&O therFlag0=servi ces.web.stats.P ostCSDelivery&O therFlag0par=de liv
Code=web.co.uk% product_250p&Tr anID=14077741&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% product_250p&Tr anID=14077757&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Code=web.co.uk% 2Fpush_wallpape r_250p&TranID=1 4077770&OtherFl ag0=services.we b.stats.PostCon tentDelivery&Ot herFlag0par=del iv
Code=web.co.uk% product_250p&Tr anID=14077604&O therFlag0=servi ces.web.stats.P ostContentDeliv ery&OtherFlag0p ar=deliv
Hint: use LIKE.
Kind regards
robert
--something like this:
declare @tranid_positio n int, @amp_position int,@string varchar(8000),
@rest_of_string varchar(8000), @Result_string varchar(8000)
set
@string='Code=w eb.co.uk%produc t_free&TranID=1 4077576&OtherFl ag0=services.we b.stats.P*ostCS Delivery&OtherF lag0par=deliv
'
set @tranid_positio n=charindex('&T ranID=',@string )
set @rest_of_string = substring(@stri ng,@tranid_posi tion+8,8000)
set @amp_position=c harindex('&',@r est_of_string)
set @Result_string= left(@rest_of_s tring,@amp_posi tion-1)
select @Result_string
Here you go...
CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO
INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk %product_250p&T ranID=14078800& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %product_free&T ranID=14077576& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077583& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077584& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_150p&T ranID=14077579& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077603& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077741& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077757& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %2Fpush_wallpap er_250p&TranID= 14077770&OtherF lag0=services.w eb.stats.PostCo ntentDelivery&O therFlag0par=de liv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077604& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
SELECT
SUBSTRING(Query String,
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)-7
)
AS TransID
FROM QueryStringTest ORDER BY TransID
DROP TABLE QueryStringTest
You could also look into regular expressions.
That worked perfectly.
Much appreciated.
I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example
OtherID=1638256 785230&TranID=1 2345
How do I edit the script to capture these records?
Regards,
Ciarán
There may be a better way but this should work...
CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO
INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk %product_250p&T ranID=14078800& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %product_free&T ranID=14077576& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077583& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077584& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_150p&T ranID=14077579& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077603& OtherFlag0=serv ices.web.stats. PostCSDelivery& OtherFlag0par=d eliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077741& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077757& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk %2Fpush_wallpap er_250p&TranID= 14077770&OtherF lag0=services.w eb.stats.PostCo ntentDelivery&O therFlag0par=de liv'
UNION SELECT
'Code=web.co.uk %product_250p&T ranID=14077604& OtherFlag0=serv ices.web.stats. PostContentDeli very&OtherFlag0 par=deliv'
UNION SELECT
'OtherID=163825 6785230&TranID= 12345'
SELECT
CASE WHEN
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)
) = 0
THEN
RIGHT(QueryStri ng,
LEN(QueryString )-
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)-6
)
ELSE
SUBSTRING(Query String,
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryStri ng)
)-7
)
END AS TransID
FROM QueryStringTest ORDER BY TransID
DROP TABLE QueryStringTest
(ch********@hot mail.com) writes: I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example
OtherID=1638256 785230&TranID=1 2345
How do I edit the script to capture these records?
Here is a query, a little different from figitals:
SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('Tran ID=', str) + len('TranId='),
len(str))
FROM QueryStringTest ) AS a) AS b
By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Raj |
last post by:
Hi all,
Can anyone help me with a script which would delete files or
move them to a different folder at some scheduled time..!
Please.....!!!
Thanks in advance...
|
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: techquest |
last post by:
got slightly similar table.
#Temp Table:
name,name1,name2,exam,score
A,A1,A21,
A,A1,A21,math100,88
A,A1,A21,math101,56
A,A1,A21,
A,A1,A21,math102,67
A,A1,A21,
A,A1,A21,math104,45
|
by: Alan Bashy |
last post by:
Please, guys, In need help with this. It is due in the next week. Please,
help me to implement the functions in this programm especially the first
three constructor. I need them guys. Please, help me.
This was inspired by Exercise 7 and Programming Problem 8 in Chapter 3 of
our text.
I have done Exercise 7 for you: Below you will find the ADT specification
for a string
of characters. It represents slightly more that a minimal string...
|
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical make-believe
challenge in order to avoid confusing the issue further.
Suppose I was hosting a dinner and I wanted to invite exactly 12 guests
from my neighborhood. I'm really picky about that... I have 12 chairs
besides my own, and I want them all...
| | |
by: jaggee |
last post by:
Hello,
This is regarding log analysis of a web system, I am finding my backend SQL programming has taken so much of time to process the application due to following quires for a log table having 500 million records, i have already done index tuning , but no use, so I would like to change the following quires into a single stored procedure in order to reduce Client server network traffic and to increase performance.
Anybody can help me to...
|
by: Amanda |
last post by:
I have tried everything with this!
I get an error stating "Index was outside the bounds of the array"
My code looks like this....
xmlDoc = New XmlDocument()
xmlDoc.Load("xml.doc")
xslDoc = New XslTransform()
|
by: RCapps |
last post by:
When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'.
For some reason it is only returning the first 11 chars of the column name?
Any help would be greatly appreciated...
This query searches a DB and determines which columns are 'Invalid' if the column name is >30 and...
|
by: zaina |
last post by:
hi everybody
i am nwebie in this forum but i think it is useful for me and the member are helpful
my project is about connecting client with the server to start exchanging messages between them.
to be more clear we process this purpose we serve this to the student in the university. how??
student will send a message that contains his name,id and request by format the server want such as zaina-20024008-grade.
the grade is the request...
|
by: Jonathan Wood |
last post by:
I'm trying to duplicate an HTML sample I have using my ASP.NET pages.
The sample contains the following within the <headtag:
<script type="text/javascript" src="flashobject.js"></script>
<script type="text/javascript">
// <!.indexOf('=');
if( pos == -1 )
{
continue; // Look for "name=value"
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
