asked on

Simple SQL string extraction

I want a simple string function that pulls a 1 of the end of a string in a column only if a 1 exists.. I was hope NOT to have to use CASE but simply something like a substring and replace or something

Patrick Matthews

Why not use CASE?

SELECT SomeColumn, CASE WHEN SomeColumn LIKE '%1' THEN '1' ELSE '' END AS JustTheOne
FROM SomeTable

Open in new window

Or if you prefer null:

SELECT SomeColumn, CASE WHEN SomeColumn LIKE '%1' THEN '1' ELSE NULL END AS JustTheOne
FROM SomeTable

Open in new window

Lee

Can you clarify? Do you want to return data that has a 1 at the end of a string or do you want a calculated column that returns a 1 if a string in another column contains a 1 at the end? An example few rows would clear things up a bit.

Lee

Patrick Matthews

Returning a number:

SELECT SomeColumn, CASE WHEN SomeColumn LIKE '%1' THEN 1 ELSE 0 END AS JustTheOne
FROM SomeTable

Open in new window

Or if you prefer null:

SELECT SomeColumn, CASE WHEN SomeColumn LIKE '%1' THEN 1 ELSE NULL END AS JustTheOne
FROM SomeTable

Open in new window

cheryl9063

ASKER

I want the field tree1

to be

tree

unless its

treet

only remove the 1 if there is a 1 at the end of the string

cheryl9063

ASKER

the column data type is nvarchar

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cheryl9063

ASKER

Thanks