Is there an "easy" way to do this?

Question

Is there an "easy" way to do this?

cory.bullard76

Ten Centuries

Points: 1232
More actions
August 2, 2016 at 10:57 am

#310208

Hello, I consider myself to be decent at SQL....I don't use it as much anymore. I have a column of data that I only want to pull the ID from. The field is called BODY. It contains:
Discontinued Rx # 0011122657 via Prescription File
I just want to pull 0011122657
is that doable?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

cory.bullard76 (8/2/2016)
Hello, I consider myself to be decent at SQL....I don't use it as much anymore. I have a column of data that I only want to pull the ID from. The field is called BODY. It contains:
Discontinued Rx # 0011122657 via Prescription File
I just want to pull 0011122657
is that doable?

there are several ways this can be achieved, but given the single row example you provided, its hard to suggest what is correct.

can you please post some more representative sample data.

eg

need to know if digits are always the same length

need to know if digits are always in the same position

need to know if digits are always preceeded by #

yadda

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 2

Sorry....yes to all of your questions. All of the data looks just like the example

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

maybe

DECLARE @STR VARCHAR(100) = 'Discontinued Rx # 0011122657 via Prescription File'

SELECT SUBSTRING(@str,19,10)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Michael L John One Orange Chip Points: 27045 More actions · Answer 4

cory.bullard76 (8/2/2016)
Sorry....yes to all of your questions. All of the data looks just like the example

In that case:

SELECT REPLACE(REPLACE(REPLACE(FieldName, 'Discontinued Rx #', '') 'via Prescription File', ''), ' ', '')

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Going further on J Livingston's formula.

DECLARE @STR VARCHAR(100) = 'Discontinued Rx # 0011122657 via Prescription File'

SELECT SUBSTRING(@str,19,10) AS FixedLength_FixedPos,

SUBSTRING(@str,PATINDEX('%[0-9]%',@str),10) AS FixedLength_VarPos,

SUBSTRING(@str,19,PATINDEX('%[0-9] %',@str)-18) AS VarLength_FixedPos,

SUBSTRING(@str,PATINDEX('%[0-9]%',@str),PATINDEX('%[0-9] %',@str)-PATINDEX('%[0-9]%',@str) + 1) AS VarLength_VarPos

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182884 More actions · Answer 6

Eirikur Eiriksson

SSC Guru

Points: 182884

August 2, 2016 at 12:16 pm

#1893314

You may want to look at these scripts[/url]

😎