SQL Query help needed - SQL Server

Hi,

I have the data values like this for a column:

Project1Module
Project2Module
.........................
Project10Module
.........................
Project100Module


I want to pickup the integer values from these data. For example:

If the value is  "Project1Module" Then 1
If the value is  "Project10Module" Then 10
If the value is  "Project100Module" Then 100

I can't use directly

convert(int,substring(column, 7, 1))

Because it may return values upto 1, 2, 3, 4, 5, 6, 7, 8, 9 but not 10. If it is 10 then we will get 1.

Can you please provide me the SQL Query ?

Thanks
milani_lucieAsked:
Who is Participating?
 
SharathData EngineerCommented:
Do you have data in the format of Project<n>Module always? If so you can just try like this.
SELECT CONVERT(INT,REPLACE(REPLACE(col,'Project',''),'Module','')) 
  FROM your_table

Open in new window

0
 
sarabhaiCommented:
try this one..

SELECT CONVERT (int,  SUBSTRING (column , CHARINDEX ('t',column)+ 1 ,(  CHARINDEX ('M',column) - CHARINDEX ('t',column)+ 1 )  )  )
0
 
sarabhaiCommented:
SELECT CONVERT (int,  SUBSTRING (column , CHARINDEX ('t',column)+ 1 ,(  CHARINDEX ('M',column) - CHARINDEX ('t',column)+ 1 )  )  )   FROM TABLE
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
dba2dbaCommented:
This should get you the desired value as output (As long as the Data Values are in the format of ProjectXXModule)

print substring('Project1Module',8,(len('Project1Module')-13))
print substring('Project10Module',8,(len('Project10Module')-13))
print substring('Project100Module',8,(len('Project100Module')-13))
print substring('Project1000Module',8,(len('Project1000Module')-13))
print substring('Project10000Module',8,(len('Project10000Module')-13))

Thanks,
0
 
dba2dbaCommented:
You could use the columnname in the place of the the Project1Modeule, Project100Module in the above query...

Thanks,
0
 
DataFieldCommented:
You can take dba2dba's select stmt one step further...

SELECT SUBSTRING(column, PATINDEX('%[0-9]%', column), LEN(column)-((PATINDEX('%[0-9]%', REVERSE(column))) - 1 + PATINDEX('%[0-9]%', column) -1))
0
 
DataFieldCommented:
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT CONVERT (int,  SUBSTRING ('Project10Module' , CHARINDEX ('t','Project10Module')+ 1 ,(  CHARINDEX ('M','Project10Module') - CHARINDEX ('t','Project10Module')- 1 )  )  )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.