Extract number from a string

Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.
Can anybody tell me how to extract a number from a string?...
TIA!!!
Hello, snigs
If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable
If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable
Razvan
PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest ;)
|||On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
>PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
>-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

>PS. With this occasion, I would like to submit my two entries for the
>"most unreadable query of the month" contest ;)
Hi Razvann,
Month, year, century - you win them all! ;-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Extract lower case characters

I need to split a string in two if there are lowercase characters at
the end of it.

For example:
'AAPLpr' becomes 'AAPL' and 'pr'
'Ta' becomes 'T' and 'a'
'MSFT' becomes 'MSFT' and ''
'TAPA' becomes 'TAPA' and ''

I am using SQL 2000. I read about "collate Latin1_General_CS_AS" but
not sure if I can use that outside of a select statement.

Thank you in advance for any help.You can specify a case-sensitive collation either on the column definition:

CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
KEY)

INSERT INTO SomeTable VALUES ('AAPLpr')
INSERT INTO SomeTable VALUES ('Ta')
INSERT INTO SomeTable VALUES ('MSFT')
INSERT INTO SomeTable VALUES ('TAPA')
INSERT INTO SomeTable VALUES ('AbCdE')

SELECT
LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
FROM SomeTable

Or, as part of a string expression:

SELECT
LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a')-1),
SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
FROM SomeTable

Result:

-- --
AAPL pr
A bCdE
MSFT
T a
TAPA

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xfKdneMR5PHz97zdRVn-hw@.giganews.com>...
> You can specify a case-sensitive collation either on the column definition:
> CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
> KEY)
> INSERT INTO SomeTable VALUES ('AAPLpr')
> INSERT INTO SomeTable VALUES ('Ta')
> INSERT INTO SomeTable VALUES ('MSFT')
> INSERT INTO SomeTable VALUES ('TAPA')
> INSERT INTO SomeTable VALUES ('AbCdE')
> SELECT
> LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
> SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
> FROM SomeTable
> Or, as part of a string expression:
> SELECT
> LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a')-1),
> SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
> FROM SomeTable
> Result:
> -- --
> AAPL pr
> A bCdE
> MSFT
> T a
> TAPA

Wonderful. Thank you for your help.sql

Extract a string in a Stored Procedure

Is there anyway to extract part of a string in a stored procedure
using a parameter as the starting point?
For example, my string might read: x234y01zx567y07zx541y04z
My Parameter is an nvarchar and the value is: "x567y"
What I want to extract is the two charachters after the parameter, in
this case "07".
Can anyone shed some light on this problem?
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Is there anyway to extract part of a string in a stored procedure
> using a parameter as the starting point?
> For example, my string might read: x234y01zx567y07zx541y04z
> My Parameter is an nvarchar and the value is: "x567y"
> What I want to extract is the two charachters after the parameter, in
> this case "07".
> Can anyone shed some light on this problem?

Looks like a combination of substring and charindex (or possibly
patindex) is what you need. I recommend that you use the SQL Server
Books Online to study all the string functions that SQL Server
offers. They are not that many, and not that extremely powerful, but
it's very useful to know them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.
I'm on a crash project using MDSE and don't have immediate access to
Books Online though...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||I figured out how to do this:

substring(mystring,charindex(@.parameter,myString)+ len(@.parameter),2)

where @.parameter = 'x' + [myUserID] + 'y'

Thanks for pointing me in the right direction.

lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I'm on a crash project using MDSE and don't have immediate access to
> Books Online though...

You have. Check my signature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Extract a character string from a text field

I need to extract a character string from a text field. The string I'm
looking for will always start with the first four characters of "ABC-" and
then will end with three numbers (0-9) in varying combinations, ex.
"ABC-508". The problem is that the position of the string in the text field
is different in each record and the last three characters of the string will
vary as described above. Any help is greatly appreciated
--
Finn GirlOne method:
SELECT
CASE
WHEN PATINDEX('%ABC-[0-9][0-9][0-9]%', MyTextCol) = 0 THEN
NULL
ELSE
SUBSTRING(MyTextCol, PATINDEX('%ABC-[0-9][0-9][0-9]%',
MyTextCol), 7)
END AS ExtractedValue
FROM dbo.MyTable
You can encapsulate the code in a proc or function for reusability.
Hope this helps.
Dan Guzman
SQL Server MVP
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl|||create table #foo (SomeColumn varchar(50))
insert into #foo values ('test ABC-508')
insert into #foo values ('testing ABC-509')
insert into #foo values ('ABC-123')
insert into #foo values ('FinnGirlABC-524')
insert into #foo values ('abcABC-456')
select * from #foo
--this shows you the charindex
SELECT CHARINDEX('ABC-', SomeColumn) FROM #foo
--this gets the desired information:
SELECT SUBSTRING(SomeColumn, (CHARINDEX('ABC-', SomeColumn)), 7) FROM #foo
drop table #foo
Keith Kratochvil
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl

extending RS for Connection String only

I know there have been many questions regarding extending RS to report off of
different data sources (xml, ado etc) - we are successfully running our
reports off of SQL Server stored procedures and are using the web services
(not url) to render directly to PDF from a custom asp .net UI. We would like
to leave this as is, tying the RS Datasource to a SQL Server stored procedure
and leaving it at that. Is it possible to use only the connection interfaces
(IDbConnection) in conjunction with the web services to programmatically
switch between datasources? Or do you have to implement the whole nine yards
(IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
custom dataset in order to switch the connection string?
Thanks,ok, I realize maybe that was a stupid question. Here is another one. If we
need to create a custom data extension in order to manipulate the connection
string, is it possible to create a 'custom' data extension that executes SQL
Stored Procedures using the .net SqlCommand for its 'custom data', seeing as
how we have the reports up and running using the stored procedures?
I am trying desperately to figure out the best approach to take. We need to
be able to switch to one of 60 + databases depending on the user requesting
the report. The custom code is in place, the reports done and working - what
would anyone suggest would be the best way to accomplish the last piece of
this puzzle?
"Myles" wrote:
> I know there have been many questions regarding extending RS to report off of
> different data sources (xml, ado etc) - we are successfully running our
> reports off of SQL Server stored procedures and are using the web services
> (not url) to render directly to PDF from a custom asp .net UI. We would like
> to leave this as is, tying the RS Datasource to a SQL Server stored procedure
> and leaving it at that. Is it possible to use only the connection interfaces
> (IDbConnection) in conjunction with the web services to programmatically
> switch between datasources? Or do you have to implement the whole nine yards
> (IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
> custom dataset in order to switch the connection string?
> Thanks,

Expression does not notice Global Variable's new value

I'm trying to do something very simple, and having a tough time with it.

I've got a Global Variable that gets a string value assigned to it in a Script Task, and then I need to access that value in Execute Process Task Expression. When running, by the time it gets to the Process Task, the global variable's value in the expression is still blank, even though a breakpoint on the task shows that it does have a value.

What am I doing wrong? This seems too simple to give me this much problem.

Make sure that you don't have another variable defined at the Script Task scope level.

Also, can you share the code you're using to set the value of the variable?|||

all variables are package scope level. I've got two variables for the script task set in the ReadWriteVariables area; pszFinCycle and ZipFile. ZipFile is the variable that has the value that will be used later in the Process Task.

Code Snippet

Dts.Variables("pszFinCycle").Value = Format(Now, "yyyyMMdd").ToString

Dts.Variables("ZipFile").Value = "EXTRACT" + Dts.Variables("pszFinCycle").Value.ToString + ".ZIP"

Dts.TaskResult = Dts.Results.Success

|||

DrinkGreen wrote:

all variables are package scope level.

Right. Make sure that when the script task is highlighted that you don't have ANOTHER variable defined at that scope. You can have multiple variables of the same name in a package that have different scopes.

The code looks good to me.|||Or that the Execute Process Task doesn't have another variable of the same name, but different scope.