WORKING WITGH STRING

Question

Post reply

WORKING WITGH STRING

sharon-472085

Ten Centuries

Points: 1182
More actions
April 26, 2011 at 4:15 am

#236232

HI,
I HAVE STRING FOR EXMPLE LIKE THIS :
nhtinhfsgjesalgjdmexSUM=190SUMdsfsdfsdfsdsdsdf
i need only the value between SUM= TO SUM
in this case i need to extract value of 120
thanks sharon

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

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Are you asking like this

declare @String varchar(1000)

select @String='nhtinhfsgjesalgjdmexSUM=190SUMdsfsdfsdfsdsdsdf'

Select SUBSTRING(@String, 1, CHARINDEX('SUM',@String)-1),REVERSE(SUBSTRING(REVERSE(@String), 1, CHARINDEX('MUS',REVERSE(@String))-1))

Thanks
Parthi

Richard Warr SSCertifiable Points: 6966 More actions · Answer 2

Hi Sharon,

Another way is:

select SUBSTRING(@s,CHARINDEX('sum=',@s,1)+4,CHARINDEX('sum',@s,CHARINDEX('sum=',@s,1)+1) - CHARINDEX('sum=',@s,1)-4)

where @s-2 is your original string.

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 3

Is this something you are looking for?

declare @Find1 varchar(20)

declare @Find2 varchar(20)

declare @search varchar(50)

select @Find1='SUM', @Search='nSUM=123123190SUMdsfsdfsdfsdsdsdf'

select @Find2='SUM='

Select SUBSTRING(@Search,

case charindex(reverse(@Find2),reverse(@search)) when 0 then 0 else len(@search) - ( charindex(reverse(@Find2),reverse(@search)) + (len(@find2)-1) ) + 1 end + 4,

case charindex(reverse(@Find1),reverse(@search)) when 0 then 0 else len(@search) - ( charindex(reverse(@Find1),reverse(@search)) + (len(@find1)-1) ) + 1 end -6)

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 4

little crazy....

try with this...changed as dynamic...for the number considering the word SUM

declare @Find1 varchar(20)

declare @Find2 varchar(20)

declare @search varchar(50)

select @Find1='SUM', @Search='nhtinhfsgjesrtyrtywqeqweqwalgjdmexSUM=192340SUMdsfsdfsdfsdsdsdf'

select @Find2='SUM='

Select SUBSTRING(@Search,

case charindex(reverse(@Find2),reverse(@search)) when 0 then 0 else len(@search) - ( charindex(reverse(@Find2),reverse(@search)) + (len(@find2)-1) ) + 1 end + 4,

case charindex(reverse(@Find1),reverse(@search)) when 0 then 0 else len(@search) - ( charindex(reverse(@Find1),reverse(@search)) + (len(@find1)-1) ) + 1 end -

case charindex(reverse(@Find2),reverse(@search)) when 0 then 0 else len(@search) - ( charindex(reverse(@Find2),reverse(@search)) + (len(@find2)-1) ) + 1 end - 4

)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

One more way

declare @String varchar(1000)

select @String='nhtinhfsgjsdereSalgjdmexSUM=1290SUMddssfsdfsdfsdsdsdf'

Select SUBSTRING(@String, 1, CHARINDEX('SUM',@String)-1),REVERSE(SUBSTRING(REVERSE(@String), 1, CHARINDEX('MUS',REVERSE(@String))-1))

select substring(@String,

charindex('SUM',@String)+len('SUM='),

charindex('SUM',@String,charindex('SUM',@String)+1)-charindex('SUM',@String)-len('SUM='))

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

srikant maurya Hall of Fame Points: 3293 More actions · Answer 6

Try This if you have only one "SUM=" per string

SELECT Substring (Substring(@String, Charindex('SUM=', @String) + 4, Len(@String) - Charindex('SUM=', @String)), 1, Patindex('%[a-z][A-Z]%', Substring(@String, Charindex('SUM=', @String) + 4, Len(@String) - Charindex('SUM=', @String))) - 1)