• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
milani_lucie
Asked:
milani_lucie
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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
 
Alpesh PatelAssistant ConsultantCommented:
SELECT CONVERT (int,  SUBSTRING ('Project10Module' , CHARINDEX ('t','Project10Module')+ 1 ,(  CHARINDEX ('M','Project10Module') - CHARINDEX ('t','Project10Module')- 1 )  )  )
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now