question

EvanStroum-9695 avatar image
0 Votes"
EvanStroum-9695 asked EvanStroum-9695 commented

Pull values to the Left of a dash

I am trying to pull from a field called "Location" that includes a number of varying lengths after a dash. I only want to pull the value of the field before the dash. My expression below worked with the LTRIM to pull values after the dash but is not working for values before. What is wrong with the below statement?

COALESCE(RTRIM(SUBSTRING(Location,CHARINDEX('-',Location,1)+1

LEN(Location)-CHARINDEX('-',Location,1))),Location)

sql-server-transact-sqlsql-server-reporting-servicesoffice-excel-itpro
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you provide an example and the output you expect?

0 Votes 0 ·

COALESCE(RTRIM(SUBSTRING(Location,CHARINDEX('-',Location,1)+1,

LEN(Location)-CHARINDEX('-',Location,10))),Location)

This is my statement that is still pulling values to the right of the dash instead of the left.

Newport Plant - 10601 - L48 Production
Sandpoint - 13501 - AK Shore QA

the expected result would be:

Newport Plant
Sandpoint

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered
 SELECT COALESCE(SUBSTRING(Location,0,
 CHARINDEX('-',Location)),Location)
 FROM #t

Output:
99276-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.


image.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @EvanStroum-9695

Please refer to:

 CREATE TABLE #t(Location char(55))
 INSERT INTO #t VALUES('Newport Plant - 10601 - L48 Production'),
                      ('Sandpoint - 13501 - AK Shore QA')
    
 SELECT LEFT(Location,CHARINDEX('-',Location)-1)
 FROM #t

Output:
99274-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EvanStroum-9695 avatar image
0 Votes"
EvanStroum-9695 answered EchoLiu-msft commented

I should have clarified that I have thousands of fields I need the values for to the left of the dash.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

99269-image.png
In fact, I was still confused when I posted the answer. The example in your comment is not consistent with the description in your question. Now I am still confused. According to your expectation, the above method works. But it seems that your problem has not been solved. I need you to provide an example and expected output that is sufficient to illustrate all aspects of the problem. This can avoid ambiguity caused by the description.

0 Votes 0 ·
image.png (13.3 KiB)

I am soooo sorry! Completely my fault and just updated. This is truly what I need and the expression that is not working.

COALESCE(RTRIM(SUBSTRING(Location,CHARINDEX('-',Location,1)+1,

LEN(Location)-CHARINDEX('-',Location,10))),Location)

This is my statement that is still pulling values to the right of the dash instead of the left.

Newport Plant - 10601 - L48 Production
Sandpoint - 13501 - AK Shore QA

the expected result would be:

Newport Plant
Sandpoint

0 Votes 0 ·

The above code applies to all SQL Server versions supported by Microsoft.Can you tell me the tool you are using?

0 Votes 0 ·
EvanStroum-9695 avatar image
0 Votes"
EvanStroum-9695 answered EchoLiu-msft edited

I am also relegated to what functions Dayforce uses for SQL. 99275-image.png



image.png (124.9 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, I am not familiar with Dayforce. The methods provided above are only for SQL Server.Are you using SQL Server?

0 Votes 0 ·

Dayforce is just the platform that has a built in SQL editor. So if the below statement gives me values to the right of a dash, why doesn't RTRIM in place of LTRIM work the same way?

COALESCE(LTRIM(SUBSTRING(Location,CHARINDEX('-',Location,1)+1,

LEN(Location)-CHARINDEX('-',Location,10))),Location)

0 Votes 0 ·

The function of LTRIM and RTRIM is to delete the spaces on the left or after the string. So it has nothing to do with returning the left or right value.
I have understood your needs, please refer to the latest answer.

0 Votes 0 ·
EvanStroum-9695 avatar image
0 Votes"
EvanStroum-9695 answered

It's a custom field editor for SQL within Dayforce. 99352-image.png



image.png (86.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EvanStroum-9695 avatar image
0 Votes"
EvanStroum-9695 answered EvanStroum-9695 commented

That worked! Thank you so much Echo! Curious, what purpose does the "FROM #t" in the expression serve?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You're welcome. #t is a temporary table I created for testing. Add # in front of the table name to indicate that this is a temporary table. Temporary tables are only used in a certain process and are not stored in the database. It is very convenient for testing or other needs to store temporary data.

0 Votes 0 ·

Got it. Thank you for the info and so sorry again about the confusion I initially caused!

0 Votes 0 ·