grab in between data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

grab in between data

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-04-28 : 23:07:43

ColumnA
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 8769 datas, 9.20USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1000 datas, 14.40USD cash"/>
'
'
'
'
'

I have a list of data as above.
How can i create another new column just to grab the data?

NewColumn
8769
1000
'
'
'
'
'

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-29 : 00:36:44

Something like this??


DECLARE  @temp TABLE (Col VARCHAR(mAX))
INSERT INTO @Temp VALUES ('<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 8769 datas, 9.20USD cash"/>'),
('<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1000 datas, 14.40USD cash"/>')

SELECT SUBSTRING(COl,PATINDEX('%- %',col)+1,5) AS NewColumn FROM @temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-04-29 : 02:44:54

SELECT SUBSTRING(COl,PATINDEX('%- %',col)+1,5) AS NewColumn FROM @temp

hi, what if the datas value appear to be lesser? This wouldnt work anymore +1,5

<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 10 datas, 14.40USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 9 datas, 14.40USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1000 datas, 14.40USD cash"/>
<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 790 datas, 14.40USD cash"/>

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-29 : 04:54:25

Hey peace
you did not said about this condition at beginning
Any way here it is...




DECLARE  @temp TABLE (Col VARCHAR(mAX))
INSERT INTO @Temp VALUES ('<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 8769 datas, 9.20USD cash"/>'),
('<Dcc::Applicable v="False"/><Dcc::DccData v="0"/><DESC v="DataReceived- 1025 datas, 14.40USD cash"/>')

SELECT (CASE WHEN PATINDEX('%[A-Z]%',NewColumn)<> 0 THEN LEFT(NewColumn,PATINDEX('%[A-Z]%',NewColumn)-1) 
	   ELSE NewColumn END)	 AS NewColumn
FROM (SELECT SUBSTRING(COl,PATINDEX('%- %',col)+1,5) AS NewColumn FROM @temp)  a

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-04-29 : 05:16:13

Hi MuralikrishnaVeera,

Was just curious incase there're more or less digits.

Thanks

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-29 : 05:21:23

And there is one more way ....

SELECT LEFT(SUBSTRING(COl,PATINDEX('%- %',col)+1,LEN(col)),PATINDEX('%[A-Z]%',SUBSTRING(COl,PATINDEX('%- %',col)+1,LEN(col)))-1) AS NewColumn FROM @temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources