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)

Extracing a House Number

I am trying to Extract the House Number from a address field

i want to start on the left and grab everything till i find the first space.

any help is greatly appreciated

select LEFT(address, (CHARINDEX(' ', address)-1)) as HouseNum

from Table

|||

Could you post some sample address data with house/unit/apt #? Note that this will be a tough problem to solve depending on your address data. It is easy to write specific SQL/TSQL expressions to extract various parts of the address but the possibilities are numerous depending on the data. There are many variables like following:

Does the addresses belong only to the USA?

Do you have different line numbers for the address that can contain the unit#?

What about the format of the addresses?

Are the addresses normalized in any manner?

So this can be a non-trivial approach and depending on your requirements it will be tough to solve in TSQL. It is best to do the address cleansing outside of the database. There are lot of commercial packages / products available to do this type of data cleaning. They can easily correct address data and normalize them to various fixed formats depending on the type of address. You can then get individual fields like zip, city, state, unit#, street# etc and use it in your application.

|||Agree. My SQL statement is only used to extract information from a string. Don should base on his situation to decide which is the best solution.|||

if the objective is only to get the house number only then

he should grab the first consecutive non alpha characters instead

save the results and have an encoder to the dirty job

|||thanks all the suggestion helped alot.

External scripts / imports / updates

Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.

As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.

Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.

FYI, they are running SQL 2000 on both XP Pro and W2K3 server.

Thanks
StarbYou can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.|||You can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.
Thanks. Dont want to use a third party tool (OSQL etc) and can't use such as DTS and Exec as it is the 'End User' that will use the funtion. It must be run via a simple script from the Mac app.

Cheers
Starbsql

Extending the number of rows returned per page in RS

Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.

Anyone out there have any thoughts on how this might be accomplished?

Thanks!

Travis

Here are a couple of options:

- set PageBreakAtEnd and PageBreakAtStart to false on your tables/matrices/grouping panels

- make your row heights smaller, use a smaller font

- filter your data better, or group it into smaller groups and have each group rendered into its own table, turn off paging on those tables

But turning off paging looks okay when viewing the report in the browser, but can make the report look like *** when exported to PDF.

extending function: view dependencies

So I have a number of separate databases on my SQL 2005 Server.

I also have a number of Reports in SSRS.

Many of the stored procedures in the various databases reference tables, functions, and stored procedures in other databases on the same server.

How can I accomplish the effect of right clicking on a stored procedure for instance, clicking view dependencies, and having everything show up in that list, not just the items in that DB?

It is all stored in the database in one form or another, but I must be missing some crucial piece to integrating it all together.

Note: I did not design this system, just maintaining and modifying existing items. There are no schemas to speak of. SSRS will most likely move to a dedicated server at one point with other data warehousing functions, so the ability to span servers would be useful.

Thanks!

Matt

Mhmm, I think this is not possible unless you parse the procedue on your own. There are no entries for dependencies in the system tables created for non-db-local objects. Additionally you can′t use schemabinding for maintainance as schemabinding applies only to two part names.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Are there any third party tools that would combine all this information into an enterprise version of "view dependencies"?

thanks|||

Hi,

Jens is correct. There is a lot of confusion about "sysdepends", so we've added a new section explaining SQL dependencies in a web refresh section of the BOL. Please read:

SQL Server 2005 Books Online

Understanding SQL Dependencies

New: 5 December 2005
http://msdn2.microsoft.com/en-us/library/ms345449.aspx

Re: " any third party tools" - A quick MSN search turned up

http://www.red-gate.com/products/sql_dependency_tracker/index.htm

which I have never used.

Regards

Ext. SPs

I need to be able to create my own MSSQ UDF to return the current system
date, offset by a configurable number of minutes, depending on a value in a
table. The reason I am trying to do this is, I want to be able to, for
testing purposes, fake the system into thinking that time has elapsed.
Changing the time on the computer is not an option.

I would like to call the UDF MyGetDate and to replace all code occurrence of
getdate() in the database with this call. This includes column default value
constraints and stored procedures.

The problem is that MSSQL does not allow the function GETDATE with a UDF. I
thought to try an fake it out by having the UDF call a SP, which in turn
called GETDATE. When I did this, I got the error 'Only functions and
extended stored procedures can be executed from within a function.'

I guess I can go down the road to try and learn how to write an extended
stored procedure to return the current time, but I imagine that there is a
learning curve here.

I realize that all COLUMN default CONSTRAINT with GETDATE could be handled
by create ADD AND UPDATE TRIGGERS that populate thisIt's a kludge but you can create a UDF that reads a value from a table.
Then schedule a Sql Agent Job to run every minute, updating that
table's value.|||Chad (chad.dokmanovich@.unisys.com) writes:
> I need to be able to create my own MSSQ UDF to return the current system
> date, offset by a configurable number of minutes, depending on a value
> in a table. The reason I am trying to do this is, I want to be able to,
> for testing purposes, fake the system into thinking that time has
> elapsed. Changing the time on the computer is not an option.
> I would like to call the UDF MyGetDate and to replace all code
> occurrence of getdate() in the database with this call. This includes
> column default value constraints and stored procedures.
> The problem is that MSSQL does not allow the function GETDATE with a
> UDF. I thought to try an fake it out by having the UDF call a SP, which
> in turn called GETDATE. When I did this, I got the error 'Only functions
> and extended stored procedures can be executed from within a function.'
> I guess I can go down the road to try and learn how to write an extended
> stored procedure to return the current time, but I imagine that there is a
> learning curve here.

Using a UDF in all sorts of constraints, could have performance issues,
and if that UDF calls an extended procedure that does not make things
better.

You could save the show with:

CREATE FUNCTION kalle(@.d datetime) RETURNS datetime AS
BEGIN
RETURN dateadd(DAY, 12, @.d)
END
go
select dbo.kalle(getdate())

Yes, that will be somewhat bulkier, but it should get the job one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Expression.Like for numbers?

Hi,

Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?

Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.

Cheers,

koekie

You can cast the BIGINT to varchar ad test that like:

WHERE CONVERT(VARCHAR(20), ColName) LIKE '%678%'

|||

In other words, something like this:

Expression.Sql("CONVERT(VARCHAR(20), {alias}.ColName) LIKE ?", "%678%", NHibernateUtil.String )

|||

Cool, thanks a lot.

Exporting Stored Procs

Hi,
Novice user here. I am developing a large number of stored procedures
and user defined functions and I want to be able to export them everynight
for back up. I know that I can user the Query Analyzer tool to export them
to .sql files one at a time, but I am approaching about 100 procedures and
functions and that can be quite tedious.
Anyone know how I can batch export a set of procs at one time?
JDWhy don't you just make a special backup of the database every night?
And have you considered using source control for database objects?
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:OmpYe.261467$E95.192029@.fed1read01...
> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export
> them
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>|||Joe
One way to do this is to create a SQL Server Job scheduled to run at the
desired frequency to run an ActiveX Task that scripts out the Stored
Procedures.
Below is a VBScript example that can be used to script all Stored Procedures
in a Database to a file.
~~~
Const LOG_FILE = "c:\script.sql"
Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to script
exists
Const DB_NAME = "pubs" ' This could be replaced with a loop to script all DB
's
' FSO I/O Mode
Const FORWRITING = 2
Const FORAPPENDING = 8
' DMO Scripting Constants
Const SQLDMOScript_ObjectPermissions = 2
Const SQLDMOScript_Default = 4
Const SQLDMOScript_OwnerQualify = 262144
Dim oStoredProcedure
Dim oSQLServer
Dim oDatabase
Dim oFSO
Dim sTexttoWrite
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oSQLServer.LoginSecure = True
oSQLserver.Connect(SQL_INSTANCE)
Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedures
If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
' Script the Object Permissions and Owner
sTexttoWrite =
oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Script
(SQLDMOScript_Default
+ SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
End If
Next
oSQLServer.DisConnect
Set oFSO = Nothing
Set oStoredProcedure = Nothing
Set oSQLServer = Nothing
~~~
- Peter Ward
WARDY IT Solutions
"Joe Delphi" wrote:

> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export the
m
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>
>|||if your problem is only scripting then
you can use Entrprise Manager> All Tasks > Genrate sql secript
you can select all sprocs at a time to .sql
Regards
R.D
"P. Ward" wrote:
> Joe
> One way to do this is to create a SQL Server Job scheduled to run at the
> desired frequency to run an ActiveX Task that scripts out the Stored
> Procedures.
> Below is a VBScript example that can be used to script all Stored Procedur
es
> in a Database to a file.
> ~~~
> Const LOG_FILE = "c:\script.sql"
> Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to scrip
t
> exists
> Const DB_NAME = "pubs" ' This could be replaced with a loop to script all
DB's
> ' FSO I/O Mode
> Const FORWRITING = 2
> Const FORAPPENDING = 8
> ' DMO Scripting Constants
> Const SQLDMOScript_ObjectPermissions = 2
> Const SQLDMOScript_Default = 4
> Const SQLDMOScript_OwnerQualify = 262144
>
> Dim oStoredProcedure
> Dim oSQLServer
> Dim oDatabase
> Dim oFSO
> Dim sTexttoWrite
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oSQLServer.LoginSecure = True
> oSQLserver.Connect(SQL_INSTANCE)
> Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
> For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedure
s
> If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
> ' Script the Object Permissions and Owner
> sTexttoWrite =
> oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Scri
pt(SQLDMOScript_Default
> + SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
> oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
> End If
> Next
> oSQLServer.DisConnect
> Set oFSO = Nothing
> Set oStoredProcedure = Nothing
> Set oSQLServer = Nothing
> ~~~
> - Peter Ward
> WARDY IT Solutions
>
> "Joe Delphi" wrote:
>|||Joe
In new groups. If you have a question do post as question. Never select as
comment. Question attract more responses.
Regards
R.D
"R.D" wrote:
> if your problem is only scripting then
> you can use Entrprise Manager> All Tasks > Genrate sql secript
> you can select all sprocs at a time to .sql
> Regards
> R.D
> "P. Ward" wrote:
>|||"R.D" <RD@.discussions.microsoft.com> wrote in message
news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> Joe
> In new groups. If you have a question do post as question. Never select as
> comment. Question attract more responses.
> Regards
> R.D
In the English language, this is considered a question:
"Anyone know how I can batch export a set of procs at one time?"|||I think one of the frilly web guis to the newsgroup allows you to categorize
a post as a comment or a question, for some unknown reason.
A
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:k1yYe.261492$E95.127285@.fed1read01...
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> In the English language, this is considered a question:
> "Anyone know how I can batch export a set of procs at one time?"
>
>|||Joe
That is the problem. The people who knows only one language interpret in
terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
what I meant was when you select new, you select it as a question for which
question mark appears besides your post.
Aaron
I dont expect those words that downgrade the norms of newgroups, from an MV
P.
Better ask MS why there are two varieties(comments and questions)
Regards
R.D
"Aaron Bertrand [SQL Server MVP]" wrote:

> I think one of the frilly web guis to the newsgroup allows you to categori
ze
> a post as a comment or a question, for some unknown reason.
> A
>
> "Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
> news:k1yYe.261492$E95.127285@.fed1read01...
>
>|||On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:

>Joe
>That is the problem. The people who knows only one language interpret in
>terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
>THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
>what I meant was when you select new, you select it as a question for which
>question mark appears besides your post.
>Aaron
>I dont expect those words that downgrade the norms of newgroups, from an M
VP.
>Better ask MS why there are two varieties(comments and questions)
>Regards
>R.D
Hi R.D.,
What you seem to miss is the fact that these groups are actually usenet
groups, which be used in many ways. The oldest form includes the use of
some dedicated software that can access usenet groups. I use Agent for
example; Joe and Aaron appear to use Outlook Express.
Usenet groups have been around for some decades already. They were quite
popular before "Internet" became a hype.
Nowadays, having a "Forum", "Message board", or other "Community" on a
web site is the fashionable thing. Many companies have recognised the
value of those, but also recognise the value and strength of the
existing usenet groups. So they create a website that appears to be
their "own" message board, but that actually is just a mirror of one or
more usenet groups. Microsoft's community pages are an example of such a
portal; others are dbforums.com, examnotes.net, tech-archive.net,
and of course groups.google.com. All posts you read on those web sites
are taken from a usenet group, and all posts you write there are
directly forwarded to the same group.
Some of those web portals decided to add some extra frills and buttons.
If I recall correctly, the Microsoft site has buttons to say if a post
is usefull or not, and some kind of rating for authors. You seem to be
using thhat site; from your comments I understand that they also offer a
way to distinguish "questions" from "comments".
All that is fine for those who use that site. But it's not supported by
the decades-old usenet architecture. So the result is that the MS site
can only present the extra info for posts that originate from their own
site.
If you, like Aaron, me, and a bunch of other "regulars" in these groups,
attempt to keep up with several hundred new messages each day, and reply
to a dozen or more each day then you'll quickly find that the interface
offered by any of those web portals will constantly get in your way.
You'll want a program that allows you to download all new messages, that
will keep track of which messages you have already read and which
discussions you've decided to skip, etc.
If I had to find each message I post at the Microsoft site, only to make
a few mouseclicks to show others if it's a question or a comment (and
then probably do the same on a few other sites as well), that would cost
me so much time that my total contribution to these groups would be
reduced severly. And it would cost me so much energy that I'd probably
stop contributing and find another hobby before the month is over.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
This is point is still valid.
Regards
R.D
"Hugo Kornelis" wrote:
> On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:
>
> Hi R.D.,
> What you seem to miss is the fact that these groups are actually usenet
> groups, which be used in many ways. The oldest form includes the use of
> some dedicated software that can access usenet groups. I use Agent for
> example; Joe and Aaron appear to use Outlook Express.
> Usenet groups have been around for some decades already. They were quite
> popular before "Internet" became a hype.
> Nowadays, having a "Forum", "Message board", or other "Community" on a
> web site is the fashionable thing. Many companies have recognised the
> value of those, but also recognise the value and strength of the
> existing usenet groups. So they create a website that appears to be
> their "own" message board, but that actually is just a mirror of one or
> more usenet groups. Microsoft's community pages are an example of such a
> portal; others are dbforums.com, examnotes.net, tech-archive.net,
> and of course groups.google.com. All posts you read on those web sites
> are taken from a usenet group, and all posts you write there are
> directly forwarded to the same group.
> Some of those web portals decided to add some extra frills and buttons.
> If I recall correctly, the Microsoft site has buttons to say if a post
> is usefull or not, and some kind of rating for authors. You seem to be
> using thhat site; from your comments I understand that they also offer a
> way to distinguish "questions" from "comments".
> All that is fine for those who use that site. But it's not supported by
> the decades-old usenet architecture. So the result is that the MS site
> can only present the extra info for posts that originate from their own
> site.
> If you, like Aaron, me, and a bunch of other "regulars" in these groups,
> attempt to keep up with several hundred new messages each day, and reply
> to a dozen or more each day then you'll quickly find that the interface
> offered by any of those web portals will constantly get in your way.
> You'll want a program that allows you to download all new messages, that
> will keep track of which messages you have already read and which
> discussions you've decided to skip, etc.
> If I had to find each message I post at the Microsoft site, only to make
> a few mouseclicks to show others if it's a question or a comment (and
> then probably do the same on a few other sites as well), that would cost
> me so much time that my total contribution to these groups would be
> reduced severly. And it would cost me so much energy that I'd probably
> stop contributing and find another hobby before the month is over.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>