By ABCD always


2019-09-11 09:13:29 8 Comments

I want to add prefix in my result.

I tried below SQL code to get my result.

Select * 
FROM human_resources hr
      left JOIN account_group ac
    ON ac.NAME = hr.user_name 
   AND ac.adgroup = hr.name
left join controll_aduser qa 
    on right(hr.user_name, length(hr.user_name) - 7) = UPPER(qa.user_name)
left join employee e
    on qa.employee_code = e.employee_code

I am not getting any error related to above SQL query but the problem is in table human_resources user_name is mention for eg: DI-SHA\QALAM and I want to add DI-SHA\ in Table control_aduser field because in that table user_name is mentioned as 'qalam' in small letters.

2 comments

@jeprubio 2019-09-11 09:22:29

In postgresql you can concatenate strings with the || operator:

SELECT 'DI-SHA\' || UPPER(hr.user_name) 
FROM human_resources hr 
  left JOIN account_group ac 
         ON ac.NAME = hr.user_name 
        AND ac.adgroup = hr.name 
  left join controll_aduser qa 
        on right(hr.user_name, length(hr.user_name) - 7) = UPPER(qa.user_name) 
  left join employee e 
         on qa.employee_code = e.employee_code

@ABCD always 2019-09-11 09:26:41

what if 'DI-SHA\' is there and one more is used in user_name i.e. 'RAWAT\' then it will take only 'DI-SHA\' only and user_name with 'RAWAT\AALAM' would not be retrieved.

@jeprubio 2019-09-11 09:29:28

I'm not sure of what are you asking now. Maybe if you have an example. Anyway, I'm pretty sure it can be done with conditional expressions and pattern matching

@ABCD always 2019-09-11 09:42:37

for eg: table 1 table 2 DI-SHA\AALAM aalam or table1 can contain RAWAT\ prefix also so how pattern matching will be done in sql or I have to use CASE logic statement.

@jeprubio 2019-09-11 09:46:11

I still don't understand the conditon when 'DI-SHA\' has to be concatenated and when 'RAWAT\' and when 'AD-ONE\'. You could edit the question to be more specific with this examples so we can try to help

@Neil Norris 2019-09-11 09:18:21

You just CONCAT() the USERNAME and wrap it with an UPPER()

concat('DI-SHA\',UPPER(USER_NAME))

Related Questions

Sponsored Content

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2563467 View
  • 1734 Score
  • 29 Answer
  • Tags:   sql duplicates

20 Answered Questions

[SOLVED] How to delete duplicate rows in SQL Server?

10 Answered Questions

[SOLVED] Difference between left join and right join in SQL Server

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

21 Answered Questions

[SOLVED] SQL select join: is it possible to prefix all columns as 'prefix.*'?

  • 2008-12-01 03:15:58
  • foxdonut
  • 128885 View
  • 187 Score
  • 21 Answer
  • Tags:   sql join

Sponsored Content