CODEWITHSUNDEEP
sqlsql-serverdatabase
Pankaj Jarial
Pankaj Jarial

Reputation: 71

SQL cast a string value

I have to get horsepower field in database as integer but it contain value like below

330hp/@6,000
30hp/@3,000
1000hp/@6,000

Can Anyone suggest how can I cast this into integer?

I only need

330
30
1000

-- Cheers

Upvotes: 0

Views: 178

Answers (4)

StackUser
StackUser

Reputation: 5398

Try this

SELECT LEFT(col1, PATINDEX('%hp%', Col1) - 1) FROM MyTable

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44316

This will work. If there is no 'h' in the text, it will return blank. If you want to cast, go ahead. But make sure you data is validated first.

Starting from 0 is a nice trick to remove the first character 'h' instead of subtracting 1 which would give errors when no 'h' exists

DECLARE @t table(col varchar(20))
insert @t values
('330hp/@6,000'),
('30hp/@3,000'),
('1000hp/@6,000'),
('123')

SELECT 
  SUBSTRING(col, 0, CHARINDEX('h', col))
FROM @t

Result:

330
30
1000
blank

Upvotes: 3

ricky89
ricky89

Reputation: 1396

The below will work.

SELECT LEFT(@Test,PATINDEX('%hp/@%',@Test)-1)

Then cast the result as an int. You will want to put something like this in a try block for scenarios where you have bad data that doesn't fit the standard given.

SELECT CAST(LEFT(@Test,PATINDEX('%hp/@%',@Test)-1) AS INT)

Upvotes: 2

huMpty duMpty
huMpty duMpty

Reputation: 14460

Select LEFT(ColumnName,CHARINDEX('h',ColumnName) - 1)

Read more LEFT (Transact-SQL)

Upvotes: 2

Related Questions