charindex issue

Question

Post reply

charindex issue

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
February 10, 2017 at 8:34 am

#327097

ID = APP4005673452-45580
select
SUBSTRING('APP4005673452-45580,CHARINDEX('-','APP4005673452-45580,4)+4,4)I want to get last 2 digits from the text - that is 80 using the above function I am able to get but not sure the syntax is correct or not, Please correct it in case of any mistakes.

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

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

If you want to get the value '80' from 'APP4005673452-45580' why not use RIGHT('APP4005673452-45580', 2)?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

We are keeping remaining stuff in the same format so I don't want to change it.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

mcfarlandparkway - Friday, February 10, 2017 8:46 AM
We are keeping remaining stuff in the same format so I don't want to change it.

The same format as what? What 'remaining stuff'? If change is for the better, why not?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

mcfarlandparkway - Friday, February 10, 2017 8:46 AM
We are keeping remaining stuff in the same format so I don't want to change it.

You are absolutely not making any sense here, Thom provided exactly what you asked for so what is the problem?
😎

Thom A SSC Guru Points: 99133 More actions · Answer 5

I, too, have no idea what you mean. If, however, you have additional requirements, then do please let us know(with some context). I'm sureone of us will be able to give you an answer that covers your requirements.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101120 More actions · Answer 6

You need to add ending quotes to the strings, and not specify a third parameter for the the CHARINDEX, like so:

SELECT SUBSTRING('APP4005673452-45580', CHARINDEX('-','APP4005673452-45580') + 4, 4)
/*Or, more generally:*/
SELECT SUBSTRING(string, CHARINDEX('-',string) + 4, 4)
FROM (
VALUES('APP4005673452-45580'),('APP4005673452-455801')
) AS test_data(string)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".