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: 99127 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: 246880 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eirikur Eiriksson SSC Guru Points: 182901 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: 99127 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: 101046 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".