October 12, 2011 at 11:19 am
Hi i have a query whihc looks for a value in a table like
select name from tableA
where name='XYZ'
now i don't have a name XYZ so it returns nothing. Now instead of that how can i make somevalue to be there like a blank string
like initailly my results will be
Name
-------
but now i want something like
Name
------
blank space or an empty string
are there any functions for that
October 12, 2011 at 11:35 am
Why do you want to do that? I've never seen a business case where this is required.
Also, any reason why you can't do that client side? Seems like the perfect place to do so!
October 12, 2011 at 2:31 pm
I second that total loss of rational dbms concept on this however...this very topic was brought up just a couple days ago...http://www.sqlservercentral.com/Forums/Topic1188064-338-1.aspx
Albeit an incredibly bizarre requirement for sql to deal with there are several ways to brute force sql to do this type of thing.
_______________________________________________________________
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/
October 12, 2011 at 2:44 pm
Joe I was thinking of your analogy of using a brick to drive in a screw when I read this post. 😛 If I could remember what thread that was I going to post that t-sql hammer image from Lowell.
_______________________________________________________________
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/
October 12, 2011 at 3:18 pm
Always love the analogies - useful for rebuttals with a zealous project coordinator who wants to define the technical solution rather than state the business problem we are to solve! 😀 😎
I agree that this would be best handled client side
Sean - I think this is the thread with the pic you were referring to: http://www.sqlservercentral.com/Forums/Topic1151022-146-1.aspx
October 12, 2011 at 3:21 pm
I don't remember that thread. There was another t-sql hammer he did that was even better than that one. Maybe Lowell will happen by here and point us to his picture. It was too funny.
_______________________________________________________________
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/
October 13, 2011 at 12:49 am
May be you can try this.
select distinct case
when exists (select name
from dbo.tableA
where name ='junk') then name
else ''-- or u can write something
end
from tableA
But this will have performance impact
thanks
sarat 🙂
Curious about SQL
October 13, 2011 at 1:01 am
Sorry the previous script doesnt work as expected if the value is present in the table.
Try the below code
declare @name varchar(10)
set @name ='junk'
select distinct case
when exists (select A.name
from dbo.tableA A
where A.name =@name ) then @name
else ''-- or u can write something
end
from tableA B
thanks
sarat 🙂
Curious about SQL
October 13, 2011 at 4:15 pm
SELECT COALESCE(b.name, '') name
FROM (SELECT 'XYZ' name) a
LEFT JOIN tableA b
ON b.name = a.name
October 14, 2011 at 3:44 pm
I see the below one is mcuh fastrer + ore simple since it saveds us from creating any additioanl ables and inner joins as well
select distinct case (when a.name like '----' then ' ' else a.name end ) as Name from table1
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 14, 2011 at 3:47 pm
Performace Guard (Shehap) (10/14/2011)
I see the below one is mcuh fastrer + ore simple since it saveds us from creating any additioanl ables and inner joins as wellselect distinct case (when a.name like '----' then ' ' else a.name end ) as Name from table1
That doesn't work for the original question. 😛
select name from tableA
where name='XYZ'
That is why there is so much extra stuff going on.
_______________________________________________________________
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/
October 14, 2011 at 8:51 pm
Since I left it generic like :
select distinct case when name like '----' then ' ' else name end as Name from table1
but if needed to be specific , it could be like :
select distinct case when name like 'XYZ' then ' ' else name end as Name from table1
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 15, 2011 at 7:40 am
The point is the "generic" way you will not return any rows when the where clause does not find anything which is specifically what the OP is asking for.
_______________________________________________________________
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/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply