We help IT Professionals succeed at work.

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
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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:

SELECT
    file_name,
    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
FROM (
    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 Consultant

Commented:
@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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 Consultant

Commented:
@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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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:

SELECT
     file_name,
     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
 FROM (
     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 DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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).
Information Technology Specialist
Commented:
>>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))
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good luck on future qs mebster82.