Simple SQL string extraction

cheryl9063
cheryl9063 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

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

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
Top Expert 2010

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

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

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

Author

Commented:
the column data type is nvarchar
Using a case is the most efficient way really. There is a way using charindex but it isn't as neat.
select case
           when right(mycol, 1) = '1' then left(mycol, len(mycol) - 1)
           else mycol
       end as mycol

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial