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 non-unique records from a table

Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:

> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>

Extract MonthName

Is there a way to get the 3 digit month name like Dec from GETDATE() or any
date?SELECT Left(DateName(m, <DateColumn> ),3) FROM <TableName>
"Scott" <sbailey@.mileslumber.com> wrote in message
news:echrOus$FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Is there a way to get the 3 digit month name like Dec from GETDATE() or
> any date?
>|||Scott,
Just to cover all bases, note that the short month is not
necessarily three characters long, if one allows the possibility
of any language setting. If you need the "short month", regardless
of its length, you can parse it out of
select shortmonths from master..syslanguages where langid = @.@.langid
or you can get it indirectly this way:
select
left(
convert(nvarchar(30),getdate(),9),
charindex(space(1),convert(varchar,getda
te(),9))-1) as ShortMonth
Steve Kass
Drew University
Scott wrote:

>Is there a way to get the 3 digit month name like Dec from GETDATE() or any
>date?
>
>|||SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))
"Scott" <sbailey@.mileslumber.com> wrote in message
news:echrOus$FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Is there a way to get the 3 digit month name like Dec from GETDATE() or
> any date?
>

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 just the date from a datetime field using T-SQL

I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)

This works, but I thought there must be an easier way.

There are many ways. Easiest is to do below:

convert(varchar, sampletimestamp, 112) = @.selcteddate

|||

Something like this:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

And best to use a form like this for your where:

WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))

So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

extract information from tables

Hello Guys,

my collegue left the job and I have been chosen to work on his assignments.

He posted this question before and got the answer but I couldn't find a way to implement it in the program.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1726638&SiteID=1

I changed the database a bit as I couldn't understand his logic of putting both PayID and TransNo as this would be a linked table and should have many to many relationship

Code Snippet

TransactionID int Unchecked
TransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked
TransPayID (from TransPayLnk Table as foreign key)

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked
TransPayID (Foreign Key from Tbl_TransPayLnk)

My requirement would stay the same, I would need to put the data from these 3 tables into a single line without duplicating the records and I would need to display them in a DataGridView.

P.S: Would it be possible to have Mixed when a TransPayID is matched in Tbl_payment with more then one record rather then brining cash and cheque.

Thank you all for your help.

Bye

Gemma

The answer is already available on the given post itself. What exactly you are looking for?|||

Yes the answer is there but I changed the table structure and though I appreciate the effort of DaleJ but I can't seem to display it like this in a DataGridView Row.
Why don't you check the new table structure and please reply on this.

Thanks for your help

Gemma

Extract IBM UniData (Pick database) data into SQL Server

Good afternoon SQL dudes

Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational\ Pick\ nested relational\ multi-valued relational database) into a SQL Server?
More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system)

I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience.

Super duper, thank you SQL troopers :)Probably gonna show my age now :/ its been a while since i have done anything in Pick (over 10 years)

If tools dont already exist to do the export the easies way would be to write a small program in PickBasic that just displayed the data to screen, and a small app that runs on the PC that initates the Pick Program, and then just captures to output to a file (from what i remember alot of pc based terminal emulation programs already do that)

Just remember to calculate a checksum for the data, then re-calculate it from the captured files.

If its UniData then there are ODBC drivers available that will let you connect to the data directly.

Sorry cant be more helpfull, its been a while since i have used that technology, although i must admit, i loved working in that environment xD|||Thanks Mark - appreciated.

Checksum is a good idea.