Using CharIndex with Sql Server

I need to get the phone aspect from the file Name  from this string field in sql server - Phone Number - 6308356202

trying to combination of charindex and it now working.

Basically a select that would read the string the and find the first _ from the left and get the 10 digits of the left of the first _

\\test \Recordings\2014-06-27\MG1ADKJFD\99_58162_6308356202_161202.vox
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
It's because you need the position of the 2nd '_' so you'll need to use CHARINDEX twice:
SELECT SUBSTRING(ColName,CHARINDEX('_',ColName,CHARINDEX('_',ColName)+1)+1,10) AS PhoneNumber
FROM YourTableName

Open in new window

Scott PletcherSenior DBACommented:
I wouldn't want to depend on a fixed number of underscores appearing in the name.  I'd use the data pattern instead, like so:

    REVERSE(SUBSTRING(REVERSE(file_name), PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', REVERSE(file_name)), 10)) AS phone_number
    SELECT '\\test \Recordings\2014-06-27\MG1ADKJFD\99_58162_6308356202_161202.vox' AS file_name UNION ALL
    SELECT '\\test \Recordings\2014-06-27\MG1ADKJFD\99_58162_x1__6308356202_161202B.vox'
) AS test_data
Duy PhamFreelance IT ConsultantCommented:
@Scott:  Your query doesn't depend on fixed number of underscores, but it depends on an assumption of phone number with fixed length = 10. So I think your query is more or less the same as Victor's. What if other parts of the filename also have 10 continuous digits?
On the other hand, in general filename format should normally be the same to all the files, hence depending on the fixed number of underscores is more practical.

Just my 2 cents.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Scott PletcherSenior DBACommented:
Hmm, there are already multiple underscores in the file name.  But there is only one occurrence of a 10-digit string in the file name.  As between the two, I think the 10-digit is more unique and thus a better basis for determining which substring to pull.  At least in our shop, file name prefixes get changed all the time :-).
Duy PhamFreelance IT ConsultantCommented:
@Scott: Yes, there are multiple underscores. I just mean that, in common of cases like above, we tend to use a structured-format for filenames (for example:  <AppID>_<UserID>_<MobileNumber>_....<FileExtension>').
I believe your query will work for years until other numeric parts in the filenames above could reach to 10 digits in length :-). But we don't know the meaning of the other parts, so to me depending on fixed number of underscores is more robust than uncertain assumption that 10-digits phone number only appears one.

Anyway, either options above will have to changed if filename pattern changes ;).
Scott PletcherSenior DBACommented:
If you look closely at my code, you'll see I picked the last 10-digit number in the name.  You could have multiple 10-digit numbers before that and it wouldn't affect the code at all.  For example:

     REVERSE(SUBSTRING(REVERSE(file_name), PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', REVERSE(file_name)), 10)) AS phone_number
     SELECT '\\test \Recordings\2014-06-27\MG1ADKJFD\1234567890_9876543210_6308356202_161202.vox' AS file_name
     SELECT '\\test\Recordings\2014-06-27\MG1ADKJFD\1234567890_9876543210_123459876_6308356202_161202.vox'
 ) AS test_data
Scott PletcherSenior DBACommented:
If you prefer to do it by relative _ position, which I don't have a really big problem with per se, but then I think you should be consistent and, instead of getting 10 chars, get all chars from there to the next _ (or the end of the file name, excl. extension).
awking00Information Technology SpecialistCommented:
>>Basically a select that would read the string the and find the first _ from the left and get the 10 digits of the left of the first _<<
Do you mean find the first underscore in the filename reading from right to left, then get the 10 digits prior to that? If so,
reverse(substring(reverse(filename),charindex('_',reverse(filename)) + 1, 10))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Good luck on future qs mebster82.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.