how to write sql query using substring

Question

I have some fields in a table Employee, with fields

empID,empName,empAddress,empCity...

And all the values of empAddress field contains like "ADR 250 Candy", "ADR 330 Simla, "ADR 220 Karty" and so on.

I want a query to list otr the address without the word 'ADR'.

Eg: I want to display the address like "250 Candy", "330 Simla", "220 Karty" ....

Please help me on this

Vlad Călin Buzea · Accepted Answer · 2016-07-02 11:00:29Z

2

Try

SELECT SUBSTR(E.empAddress, 4) AS 'Address' FROM Employee E

If you want to skip the space character also replace SUBSTR(E.empAddress, 4) with SUBSTR(E.empAddress, 5)

edited Jul 2, 2016 at 11:00

answered Jul 2, 2016 at 8:54

Vlad Călin Buzea

5595 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naresh Over a year ago

The Query gives the correct answer. But while using the above(first one) i am getting the field name as SUBSTR(E.empAddress,4)

Michael Gaskill · Accepted Answer · 2016-07-03 00:33:05Z

0

try this :

SELECT EmpAddress = REPLACE(E.empAddress, 'ADR ', '') 
FROM Employee E

edited Jul 3, 2016 at 0:33

Michael Gaskill

8,06210 gold badges40 silver badges46 bronze badges

answered Jul 2, 2016 at 8:59

Siavash Rostami

1,9334 gold badges18 silver badges33 bronze badges

3 Comments

Michael Gaskill Over a year ago

"try this" is not adequate explanation for an answer. Please explain why this answers the question. Code-only solutions are considered poor content on Stack Overflow.

Will Over a year ago

Hi, could you please add some explanation to your code? This popped up in the review queue, as code-only answers tend to.

Siavash Rostami Over a year ago

well the REPLACE function does the normal replace in string job and it's a built-in SQL Server function, so i thought maybe it's self-explanatory and just put it sloppy out there. however, we have supposedly an Employee table and we're gonna replace the string ADDR with empty string in empAddress field.

Collectives™ on Stack Overflow

how to write sql query using substring

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related