August 3, 2012 at 10:43 am
I have a column " CID" which has values like this:
CID :
150569-Missing
120567-HEALTH
R60318-Missing
5403-HEALTH
40475-HEALTH
003182173-health
need to get only the numeric value ( that is digits before '-')
example '150569-Missing' need only 150569
cannot use function as I need this column on join.
Can anyone help me with this??
August 3, 2012 at 10:59 am
komal145 (8/3/2012)
I have a column " CID" which has values like this:CID :
150569-Missing
120567-HEALTH
R60318-Missing
5403-HEALTH
40475-HEALTH
003182173-health
need to get only the numeric value ( that is digits before '-')
example '150569-Missing' need only 150569
cannot use function as I need this column on join.
Can anyone help me with this??
Do you want something like:
SELECT LEFT(CID, CHARINDEX('-', CID)-1) AS BeforeDash
Now that won't be sargable, which may have been the intent of your original question.
Rob
August 3, 2012 at 11:09 am
also watch for those fields that are missing the dash:
SELECT
CASE
WHEN CHARINDEX('-', CID) >= 0
THEN LEFT(CID, CHARINDEX('-', CID) - 1)
ELSE CID
END AS BeforeDash
Lowell
August 3, 2012 at 11:59 am
Just to add to the solutions, if at all possible split that into two columns. It appears you have two pieces of information in a single column. This type of thing will cause you nothing but pain in the long run. A column should hold a single piece of information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2012 at 12:18 pm
tHANK YOU
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply