Replicate, Space, Patindex, Replace and Stuff string functions in sql server 2008 Part 24
admin July 7, 2020 37 Comments
In this video we will learn about commonly string functions in sql server 2008 like Replicate, Space, Patindex, Replace, and Stuff. We will look at some real time examples of where we can use these functions.
Text version of the video
Slides
All SQL Server Text Articles
All SQL Server Slides
All Dot Net and SQL Server Tutorials in English
All Dot Net and SQL Server Tutorials in Arabic
Nguồn: https://chaoticpharmacology.com/
Xem thêm bài viết khác: https://chaoticpharmacology.com/cong-nghe/
Xem thêm Bài Viết:
- Samsung Galaxy Trend Plus S7580 hard reset
- FPT Shop – Khui hộp Samsung Galaxy Tab 3 V – Tablet tầm trung cho người Việt
- [Review dạo] Đánh giá chi tiết Samsung Galaxy S5 LTE A G906
- HARD RESET S2 HD , E120L , E120K MỞ KHÓA MÀN HÌNH S2HD , HIEU MOBILE
- Tư vấn điện thoại Galaxy Note 5 bây giờ chỉ còn 2 đến 3 triệu liệu có đáng mua không?
RELATED ARTICLES
37 COMMENTS
LEAVE A COMMENT
Enjoying this lecture .
Hi venkat,
Could you please tell me how to write the below sql statement in Oracle
Patindex(‘%0-9%’, alphanumeric column)
How to give range in instr function??
Super explanation love to learn from you
Loved watching this
Lucid and concise. Thank you.
Come on Venkat, or someone…if could explain what is happening here… Because without CHARINDEX('@', Email)+1) at the end also return the same result :
select FirstName, LastName,
SUBSTRING(Email, 1, 2) + REPLICATE('*',5) + SUBSTRING(Email, CHARINDEX('@', Email), LEN(Email))
from tblEmployee
Venkat for president ❤️… Love your video tutorials bro, wish I could meet you one day, you are an amazing teacher
Please tell me how to extract 20-40 from column and will get output like 20*1000/40*1000
Hello Sir! I really appreciate your hard work simplifying SQL in the best way ever, I admit I never found anyone who can do it in the best way as you do. Thus, I really want to be like you, you're my role model, I am doing my best for the moment, however, I just want to know how much it took you to learn using SQL just as you do now ? A year, two … ? I am actually making sure to learn all of what you provided in here and I am learning fast, does this mean if I ever finished all of your tutorial videos, and eventually will be able to use all of the queries you provided that I am well qualified to use SQL ? I hope you would answer me so soon.
regards.
How can i split this into two columns
like
ID and Description
867 Other infectious and parasitic diseases diagnoses with mcc
Integer value in Id column and rest will be in Description column]
Perfect
How can I get the data you are working on? I would like to practice all the functions
Thanks Venkat for providing such comprehensive tutorial. I have been practicing sql questions online and came across a question to find median of a column. I would love you to do a tutorial on writing a sql for finding the median of a column in a table (for example median salary per department).
I want to split comma separated string e.q '1,2,3' as IN parameter to query
Thank You Sir
Very helpful Video..Very Nice Sir
STUFF is simpler if compared to REPLICATE function inside Substring.
Sample Code on STUFF with REPLICATE:: SELECT Name,STUFF(Email,2,3,REPLICATE('*',5)) from tblPerson;
I don't understand the idea of SPACE-function. I understand how it works, but I don't understand why it exists. SPACE(5) = REPLICATE(' ', 5), right? Why separate function is needed?
It's the best video tutorial, even if it is to compare with the courses wich I bought. I'm reading also the book Querying Microsoft SQL Server 2012 Training Kit and I can see the quality of your video's. Thank's for your work.
Hi Venkat ,your videcs are awesome and easily understandable,thanks a lot for making it
So almost all STRING functions you have described. Thank U a lot ! You are amazing!
Hi Venkat ,your videcs are awesome and easily understandable,thanks a lot for making it,i got doubt regarding patindex and charindex ……when I am using charindex it is giving me result as you said …I was trying it with patindex too…..where I got the result forselect SUBSTRING(Email,PATINDEX('%@%',Email)+1,LEN(Email)-PATINDEX('%@%',Email))as domain from tblemployee but when I use in group by it is throwing errorselect SUBSTRING(Email,PATINDEX('%@%',Email)+1,LEN(Email)-PATINDEX('%@%',Email))as domain, count(Email) from tblemployee group by SUBSTRING(Email,PATINDEX('%@%',Email)+1,len(Email)-PATINDEX('@',Email))can u suggest me y?thanks in advance…..
Hi Venkant , Your videos are of high quality . Thanks for making it . Could you please make a video of finding first monday of the month
Just for Info actual query to replace .com with .net in the table would be
UPDATE tblEmployee SET Email=REPLACE(Email,'.com','.net')
select ID, Name, SUBSTRING(Email,1,2)+REPLICATE('*',2)+SUBSTRING(Email, CHARINDEX('@',Email),LEN(Email)) from tblPerson
I think len(email) is better because anyways there are no characters after the last .domain name. It saves the trouble of calculating the remaining length but still for other situations we might need the exact remaining length. Nice explaination. Just watching one by one.
Thanks, simple and the best explanation.
Mask all characters between the first letter and the @ sign:
select *, stuff(Email, 2, len(Email) – (len(Email) – charindex('@', Email) + 2),
replicate('*', len(Email) – (len(Email) – charindex('@', Email) + 2))) as StuffedEmail from tblEmployee
thanks venkat, this is quite easy to grasp
nice explanation
Hi, Awsome. we are learing lot of things from right professional
.
I have one small doubt on Patindex . i understood that it is used with wild card.
But, select * from emailtable where email like '%@aaa.com'
retrieves the same result.
select email,PATINDEX('%aaa.com',email) as occurance
from emailtable where PATINDEX('%aaa.com',email)>0
these returns same. what is the use of pat index in this situation ?
The Best, Simply the Best…
This is free. It it were not free, I would not mind paying $50 or so to take a class from you. I follow a long and I understand it.
Thank you. I have found out that most of the videos on Youtube are better taking classes at school.
I enjoyed this video.
Friends if you want to answer what is the diff bet CHAR and PAT INDEXS in the interview then this is the best video.
Thanks a bunch
thanks .. these videos helped me alot
Nice video. Helped me in learning new functions in almost no time. Thank you Venkat.
Hi, Very soon as soon as I am done with sql server. If you can subscribe to my channel, you will be getting updates when I upload new videos. Thank You.
Venkat, Are you planning to upload tutorials for ASP.NET as well? If so when would that happen?